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
- EVALUATE operator
- Administrative utilities
- Expression indexing
a virtual datatype created through a constraint placed on a VARCHAR2 column in a user table that stores expressions
an operator that evaluates expressions for each data item
set of utilities that validate expressions and suggest optimal index structure
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
- Define an attribute set
- Create USERS table which contains a varchar2 column to store the expression
- Insert expressions in the table
- Apply the SQL EVALUATE operator in your queries
an attribute set is defined as a special oracle object type that carries all valid variables for the Expression.
assigning attribute set to expression column in the user table,varchar2 column is implicitly converted into expression datatype(virtual)
even possible to use SQL-loader
Expression Filter Usage Scenarios
- To screen incoming data
- To screen existing data for new interests, conditions, standards or rules
- To dynamically bundle up multiple queries
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)
– This new piece of data does not meet one of (y)our standards
– This record does not adhere to this business rule
-Because of this new EU regulation, we have to redesign these products
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)>
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.
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,
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),
CREATE TRIGGER activechk AFTER insert OR update ON Inventory
FOR EACH ROW
cursor c1 (ditem VARCHAR2) is
SELECT CId, Phone FROM Consumer WHERE EVALUATE (Interest, ditem) = 1;
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);
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.)
Some documents providing information on Expression Filters