Expression Filters (10g)

Looking at new features of Oracle 10g we ran into Expression Filters. Here, we will discuss our initial experiences and our conclusions as to how you can make use of Expression Filters.

What is an expression Filter?

Looking at new features of Oracle 10g we ran into Expression Filters. Expression Filters allow you to store, index and evaluate conditional expressions in a column which you can then use in the where clause of a query.

Expression Filter matches expressions in a column with a data item passed by a SQL statement or with data stored in one or more tables, and evaluates each expression to be true or false.

Expression Filter includes the following elements

• Expression datatype

a virtual datatype created through a constraint placed on a VARCHAR2 column in a user table that stores expressions

• EVALUATE operator

an operator that evaluates expressions for each data item

• Administrative utilities

set of utilities that validate expressions and suggest optimal index structure

• Expression indexing

enhances performance of the EVALUATE operator for large expression sets. Expression indexing is available in Oracle Database Enterprise Edition

Basic Steps to create and use an expression column

1. Define an attribute set

an attribute set is defined as a special oracle object type that carries all valid variables for the Expression.

2. Create USERS table which contains a varchar2 column to store the expression

assigning attribute set to expression column in the user table,varchar2 column is implicitly converted into expression datatype(virtual)

3. Insert expressions in the table

even possible to use SQL-loader

4. Apply the SQL EVALUATE operator in your queries

Expression Filter Usage Scenarios

1. To screen incoming data

Find matches with expressed interests or conditions
– We have found an item that may be exactly what you’re looking for (based on your personal preferences)
– A suspect has just entered the country (given the terrorist screening guidelines provided by the authorities)
Find non-matches
– This new piece of data does not meet one of (y)our standards
– This record does not adhere to this business rule

2. To screen existing data for new interests, conditions, standards or rules

-Because of this new EU regulation, we have to redesign these products

3. To dynamically bundle up multiple queries

Application Characteristics
Expression Filter is a good fit for applications where the data has the following characteristics:

• A large number of data items exists to be evaluated
• Each data item has structured data attributes, for example VARCHAR, NUMBER, DATE, XMLTYPE
• Incoming data is evaluated by a significant number of unique and persistent queries containing expressions
• The expression (in SQL WHERE clause format) describes an interest in incoming data items
• The expressions compare attributes to values using relational operators (=, !=, < , >, and so on)

Sample Queries

Return all consumers interested in a given car (Model=’Mustang’, Year=2000, Price=18000, Mileage=22000)

SELECT * FROM Consumer WHERE
EVALUATE (Consumer.Interest, 'Model=>''Mustang'', Year=>2000, Price=>18000, Mileage=>22000' ) = 1;

Here below a conceptual image of consumers’ interests (in trading cars) being captured in a Consumer table.

Application Examples

Sample application of screening existing data

Joining data item table(Inventory) with the expression table(Consumer) to determine the interest in each car.

SELECT DISTINCT Inventory.Model, count(*) as Demand
FROM Consumer, Inventory
WHERE EVALUATE (Consumer.Interest,
Car4Sale.getVarchar(Inventory.Model,
Inventory.Year,
Inventory.Price,
Inventory.Mileage)) = 1
GROUP BY Inventory.Model
ORDER BY Demand DESC;


Sample application for screening of incoming data

Each time a record is inserted(or updated) and a consumer is interested in the car
a message is displayed.


CREATE TABLE Inventory (Model VARCHAR2(20),
Year NUMBER,
Price NUMBER,
Mileage NUMBER);

ditem VARCHAR2(200);
BEGIN
ditem := Car4Sale.getVarchar(:new.Model, :new.Year, :new.Price, :new.Mileage);
for cur in c1(ditem) loop
DBMS_OUTPUT.PUT_LINE(' For Model '||:new.Model||' Call '||cur.CId|| ' @ '||cur.Phone);
end loop;
END;

You can also change this example and use it to enforce a business rule. For example don’t insert any cars if they do not meet the rules that describe our market-segment (e.g. no cars more expensive than $30000, no Volkswagens, no Blue Volvo’s etc.)

Sample script
ConsumerSampleScript

Presentation Amis
ExpressionFilter slides

Some documents providing information on Expression Filters

Application Developer’s Guide – Expression Filter

Manage Business Rules with Expression Filter
Oracle10g Expression Filter