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

Define an attribute set

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

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)

Insert expressions in the table

even possible to use SQL-loader

Apply the SQL EVALUATE operator in your queries

Expression Filter Usage Scenarios

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

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

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

To dynamically bundle up multiple queries

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

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.

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.)