Oracle Expression Filter

The Oracle Expression Filter allows application developers to manage and evaluate conditional expressions that describe users' interests in data. The feature consists of: an Expression datatype, a SQL EVALUATE operator, and an index (Enterprise Edition only).

The Oracle Expression Filter allows you to store conditional expressions in a column, which you can then use in the WHERE clause of a database query.

You can use the EVALUATE operator to identify the conditional expressions that return true for a given data item. For example, the following query can be issued to return all the boaters who are interested in a given boat (Model='Sunray', Length=32, Year=2002):

SELECT Name, Email FROM Boaters

WHERE EVALUATE (Boat4Sale,

'Model=>''SunRay'', Length=>32, Year=>2002') = 1;

To speed up such queries, you can create an optional Expression Filter index on the INTEREST column, this is only available in the Enterprise edition of Oracle.

The Oracle Expression Filter is an internal set of functions and procedures that enables conditional expressions to be stored, managed, and evaluated efficiently in Oracle Database 10g.

The conditional expressions, referred to as Expressions, are a useful way of describing the interest of a user regarding some expected data.

The Oracle Expression Filter provides a SQL schema and PL/SQL and Java packages that facilitate the storage, retrieval, update, and query of collections of Expressions in Oracle Database 10g. The Expression Filter consists of the following components:

n         The EXFSYS schema that determines the storage, syntax, and semantics of the Expression datatypes

n         The Expression Filter indexing mechanism

n         A set of operators and functions that evaluate the Expressions stored in user tables for a given data item

n         The Administrative utilities to validate and give advice about correct index structure

The Expression Filter feature can be installed on an Oracle 10g Standard or Enterprise Edition database. It is provided as a set of PL/SQL packages, a Java package, a set of dictionary tables, and catalog views. All these objects are created in a dedicated schema named EXFSYS. This schema is not part of the typical installation of the database and will not exist before the Expression Filter is installed. For these initial Oracle Database 10g releases, you'll need to manually install the feature, in a future release, you will not need to do so.

The script to install the Expression Filter feature is available as catexf.sql, under the $ORACLE_HOME/rdbms/admin/ directory. This script should be executed from a SQL*Plus session while connected as SYSDBA. The catexf.sql script performs the following actions:

n         Creates the exfsys schema owner using exfsys.exe

n         Loads information about the exfsys schema into the dbms_registrypackage

n         Creates the Java Expresssion library, using initexf.sql

n         Creates the exfsys objects, using exftyp.sql

n         Creates the Expression Filter Dictionary, using exftab.sql

n         Creates the public PL/SQL packages with exfpbs.sql

n         Creates the Expression filter catalog views, using exfview.sql

n         Creates the sys owned private package, using exfsppvs.plb

n         Creates the Expression filter APIs, using exfeapvs.plb

n         Creates the Expression filter indextype and operators, using exfimpvs.plb

n         Creates the Xpath Expression filter support, using exfxppvs.plb

n         Creates the indextype definition for the EXPFilter indextype

n         Associates the statistical methods with the appropriate functions

The Expression Filter feature is uninstalled using the catnoexf.sqlscript, which is found in the $ORACLE_HOME/rdbms/admin directory, or its equivalent on your operating system. The functionality of the Expression Filter is the same in both the Standard and Enterprise Edition however, the support for indexing Expressions is only available in the Enterprise Edition.

Expressions are Boolean conditions that characterize the interest of a user in some data or information. It is required that the Expressions adhere to standard SQL WHERE clause format, and only reference variables and built-in or user-defined functions in their predicates.

For example, the following expression captures the interest of a user in a boat (the data item) with the model, price, and year as attributes.

UPPER(Model) = 'POLAR' and Price < 10000 and Year > 2000

Expressions are stored in a column of the Expression datatype in an Oracle 10g database table. Expressions, stored in an Expression datatype can be inserted, updated, and deleted using standard DML statements. For queries projecting the columns holding expressions, the expressions are displayed in string format.

A set of Expressions stored in an Expression column of a database table shares a common list of variables that can be used in their predicates. Additionally, these Expressions can reference any built-in function or an approved user-defined function in their predicates. The list of variable names along with their datatypes and the list of built-in and approved user-defined functions constitute the metadata for a set of Expressions stored in a column. This metadata, referred to as the Attribute Set, determines the evaluation context (scope) for the corresponding expressions. When a new expression is added or an existing expression is modified (using INSERT or UPDATE), it is validated against the attribute set.

Let's look at how the attribute set is specified for an Expression.

The Expression Attribute Set

An Expression's attribute set is defined as a special Oracle object type that carries all the valid variables for the set. Implicitly, the attribute set includes all the built-in functions as valid references. If desired, you can add user-defined functions to the attribute set as well.

The user-specified attributes for the attribute set can be added one at a time, using the packages and procedures provided, or you can use a type definition as the source of the attribute set for an Expression. If you don't use an object type definition for the elementary attributes, the system automatically creates an object type using the name of the attribute set as the object type's name.

The following PL/SQL commands create an attribute set named Boat4Sale with a list of required elementary attributes (variables used in the expressions). First, we will look at using the method provided by the Expressions packages and procedures:

BEGIN

dbms_expfil.create_attribute_set(attr_set => 'Boat4Sale');

dbms_expfil.add_elementary_attribute(

attr_set => 'Boat4Sale',

attr_name => 'Model',

attr_type => 'VARCHAR2(20)');

dbms_expfil.add_elementary_attribute(

attr_set => 'Boat4Sale',

attr_name => 'Year',

attr_type => 'NUMBER');

dbms_expfil.add_elementary_attribute(

attr_set => 'Boat4Sale',

attr_name => 'Price',

attr_type => 'NUMBER');

dbms_expfil.add_elementary_attribute(

attr_set => 'Boat4Sale',

attr_name => 'EngineHours',

attr_type => 'NUMBER');

dbms_expfil.add_elementary_attribute(

attr_set => 'Boat4Sale',

attr_name => 'Length',

attr_type => 'NUMBER');

END;

/

Personally, I find using the provided packages and procedures a bit cumbersome and complex; it is much easier to simply define a type and use that as the source for the attribute set as is shown below:

CREATE OR REPLACE TYPE Boat4Sale AS OBJECT

(Model VARCHAR2(20),

Year NUMBER,

Price NUMBER,

EngineHours NUMBER

Length NUMBER);

/

BEGIN

dbms_expfil.create_attribute_set(attr_set => 'BOAT4SALE',

from_type => 'YES');

END;

/

Both methods result in a datatype that can be used to create a table for storing Expressions, using the specified Expression datatype.

Note: You cannot create an Expression and associated tables in the SYS schema, as the creation builds a trigger and triggers cannot be built on SYS-owned objects. Any attempt will fail with an error stack similar to:

BEGIN

*

ERROR at line 1:

ORA-38465: failed to create the privilege checking trigger due to: ORA-4089

ORA-06512: at "EXFSYS.DBMS_EXPFIL_DR", line 24

ORA-06512: at "EXFSYS.DBMS_EXPFIL_DR", line 370

ORA-06512: at "EXFSYS.DBMS_EXPFIL", line 602

ORA-06512: at line 2

The ORA-409 error is thrown when you attempt to create a trigger on a SYS-owned object.

Expression Datatype and Expressions

An Expression datatype is always a VARCHAR2 column with an associated attribute set. Any VARCHAR2 column in a user table can be converted into a column of Expression datatype by assigning a defined Expression attribute set to it. This assignment constrains the values stored in the specified column to be valid SQL WHERE clause expressions that reference only the specified Expresssion datatype attributes (elementary attributes and approved functions).

For example, the Boaters table, which stores a buyer's interest in buying pre-owned boats, would be defined as follows:

CREATE TABLE Boaters (BId NUMBER,

Zipcode NUMBER,

Phone VARCHAR2(12),

Interest VARCHAR2(200));

BEGIN

dbms_expfil.assign_attribute_set (

attr_set => 'Boat4Sale',

expr_tab => 'BOATERS',

expr_col => 'INTEREST');

END;

/

For DML purposes, the column storing the expression is treated as a VARCHAR2 column with a data constraint on it. Attempts to insert invalid expressions in such columns are rejected. A boat buyer's interest in trading cars can be added to the Boaters table using standard DML INSERT statements. For example:

INSERT INTO boaters VALUES (1, 32611, '917 768 4633',

'Model=''Sea ray'' and Price < 10000

and EngineHours < 250');

INSERT INTO Boaters VALUES (2, 03060, '603 983 3464',

'Model=''Bayliner'' and Year > 2001 and Price < 13000');

Email column is missing

What happens if we attempt to insert a non-approved function into an Expression? Let's see:

SQL> INSERT INTO Boaters VALUES (3, 03060, '603 484 7013',

   2 'SeaRating(Model, Year) > 5 and Price < 15000');

INSERT INTO Boaters VALUES

                   *

Error at line 1:

ORA-00904: "SEARATING": invalid identifier

ORA-06512: at "EXFSYS.EXF$VALIDATE_3", line 17

ORA-04088: error during execution of trigger 'EXFSYS.EXF$VALIDATE_3'

So, what can we do about this?

If the expressions need to reference additional user-defined functions, they can be added to the corresponding attribute set as shown in the following examples:

CREATE or REPLACE FUNCTION HorsePower(Model VARCHAR2, Year VARCHAR2)

return NUMBER is

BEGIN

-- Just for example since this returns same value each time

return 150;

END HorsePower;

/

CREATE or REPLACE FUNCTION SeaRating(Model VARCHAR2, Year VARCHAR2)

return NUMBER is

BEGIN

-- Just for example since this returns same value each time

return 5;

END SeaRating; ( you mean SeaRating!!!)

/

BEGIN

dbms_expfil.add_functions (attr_set => 'Boat4Sale',

funcs_name => 'HorsePower');

dbms_expfil.add_functions (attr_set => 'Boat4Sale',

funcs_name => 'SeaRating');

END;

/

Once the user-defined function SeaRating is registered for the expression set, the following Expression is now legal for insertion:

INSERT INTO Boaters VALUES (3, 03060, '603 484 7013',

'SeaRating(Model, Year) > 5 and Price < 15000');

Stored Expressions can contain predicates, conjunctions, and disjunctions. Expressions cannot contain subqueries. Expression data can also be loaded using SQL*Loader.

An individual attribute set can be used to create multiple columns of Expression datatypes in multiple tables. The tables with columns referencing an attribute set must be in the same schema as the attribute set. For example, an attribute set created in the NAUTICAL schema cannot be used to create an Expression datatype column in another schema.

All of this is well and good, but how do you use these expressions once they exist? This is the purpose of the EVALUATE operator.

The EVALUATE Operator

The EVALUATEoperator is used to evaluate an Expression set for a given data item. The Expressions to be evaluated must be stored in a column of Expression datatype, which is created by associating an attribute set to a VARCHAR2 column in a user table, as we saw in the previous sections.

Format for EVALUATE

EVALUATE (expression_column, <dataitem>)

<dataitem> := <varchar_dataitem> | <anydata_dataitem>

<varchar_dataitem> := attribute_name => attribute_value

{, attribute_name => attribute_value}>

<anydata_dataitem> := AnyData.convertObject(attribute_set_instance)

The EVALUATE operator returns 1 for an expression that evaluates to true for the data item and 0 otherwise.

The EVALUATE operator is used in the WHERE clause of a standard SQL query or DML (UPDATE or DELETE) statement. The name-value pairs for a data item can be generated using the getVarchar()method, defined for the object type associated with the corresponding attribute set.

When an Expression Filter index is defined on a column storing expressions (only in Enterprise versions), the EVALUATE operator on such a column may use the index for the expression set evaluation based on its usage cost. The EVALUATE operator can be used as a join predicate between a table storing expressions and a table storing corresponding data items.

The VARCHAR form of data item cannot be used for an attribute set with one or more binary typed attributes. For example, if one of the attributes is of CLOB type or an object type (embedded object), the AnyData form of the data item should be used.

Related views:

n         user_expfil_attribute_sets

n         user_expfil_attributes

n         user_expfil_expression_sets

Some example queries against these views are:

SQL> select * from user_expfil_attribute_sets;

ATTRIBUTE_SET_NAME                                                             

--------------------                                                           

BOAT4SALE                                                                      

BOAT4SALE2                                                                      

SQL> select * from user_expfil_attributes;

ATTRIBUTE_SET_NAME   ATTRIBUTE    DATA_TYPE       ASSOCIATED_TABLE             

-------------------- ------------ --------------- ----------------              

BOAT4SALE            MODEL        VARCHAR2(20)                                 

BOAT4SALE            YEAR         NUMBER                                       

BOAT4SALE            PRICE        NUMBER                                       

BOAT4SALE            ENGINEHOURS  NUMBER                                       

BOAT4SALE2           MODEL        VARCHAR2(20)                                 

BOAT4SALE2           YEAR         NUMBER                                       

BOAT4SALE2           PRICE        NUMBER                                       

BOAT4SALE2           ENGINEHOURS  NUMBER                                       

8 rows selected.

SQL> col expr_table format a10

SQL> col expr_column format a12

SQL> column attribute_set format a15

SQL> select * from user_expfil_expression_sets

EXPR_TABLE EXPR_COLUMN  ATTRIBUTE_SET   LAST_ANAL NUM_EXPRESSIONS PREDS_PER_EXPR NUM_SPARSE_PREDS  

---------- ------------ --------------- --------- --------------- -------------- ----------------  

BOATER     INTEREST     BOAT4SALE       30-AUG-03               3              2                0  

Let's look at an example using the EVALUATE operator.

Example use of the EVALUATE Operator

The following example query uses the VARCHAR form of data item generated by the getVarchar( ) function:

SQL> SELECT * FROM Boaters WHERE

   2 EVALUATE (Boaters.Interest,

   3 Boat4Sale('Sea ray', 2002, 9000, 0).getVarchar ()) = 1;

 BID ZIPCODE PHONE        INTEREST

---- ------- ------------ ----------------------------------------------------

   1   32611 917 768 4633 Model=>'Sea ray' and Price<10000 and EngineHours<250

For the previous query, the data item can be passed in the AnyData form with the following syntax:

SQL> SELECT * FROM Boaters WHERE

EVALUATE (Boaters.Interest,

AnyData.convertObject (

Boat4Sale ('Sea ray',2002,9000,0))) = 1;

BID ZIPCODE PHONE        INTEREST

---- ------- ------------ ----------------------------------------------------

   1   32611 917 768 4633 Model=>'Sea ray' and Price<10000 and EngineHours<250

When a large set of Expressions are stored in a table, the table storing the Expressions can be joined with the table storing data items to be evaluated. For example, let's say we have a used boats table that looks like so:

SQL> DESC USED_BOATS

Name         Datatype

---------    ----------

SELLER_ID    NUMBER

MODEL        VARCHAR2(20)

YEAR         NUMBER

PRICE        NUMBER

ENGINEHOURS  NUMBER

LENGTH       NUMBER

We could compare our list of Boaters and their expressed desires to our list of used boats using the following syntax:

SELECT u.seller_id, b.bid, b.Phone

FROM Boaters c, Used_boats u

WHERE

EVALUATE (b.Interest,

Boat4Sale(u.Model, u.Year, u.Price, u.EngineHours).getVarchar()) = 1

ORDER BY u.seller_id;

Of course, if we have a bunch of data and a bunch of Expressions, the comparisons can get pretty complicated and performance can suffer. To help with this, the Expressions can be indexed. Lets look at the concept of the Expression Index next.

Using CREATE INDEX for Expressions

The CREATE INDEXtatement can be used to create an Expression Filter index for a set of expressions stored in a column. The column being indexed should be configured to store expressions (that is, it must have an attribute set assigned to it), and the index should be created in the same schema as the table (the table that is storing the expressions).

Format for CREATE INDEX for Expressions

CREATE INDEX [schema_name.]index_name ON

[schama_name.].table_name (column_name) INDEXTYPE IS EXFSYS.EXPFILTER

[ PARAMETERS (' <parameters_clause> ' ) ...;

<parameters_clause>:= [ADD TO DEFAULTS | REPLACE DEFAULTS]

[<storeattrs_clause>] [<indexattrs_clause>][< predstorage _clause>]

<storeattrs_clause> := STOREATTRS [ ( attr1, attr2, ..., attrx ) | TOP n ]

<indexattrs_clause> := INDEXATTRS [ ( attr1, attr2, ..., attry ) | TOP m ]

<predstorage_clause> := PREDSTORAGE (<storage_clause>)

The various clauses and parameters for the CREATE INDEX command for Expressions are shown in the following table.

Usage Notes for Expression Indexes

For an Expression Filter index, all the indexed attributes are stored. Therefore, the list of stored attributes is derived from those provided in the STOREATTRS and the INDEXATTRSclause. If the REPLACE DEFAULTS clause is not specified, this list is merged with the default index parameters associated with the corresponding attribute set.

If the index parameters are directly assigned to an expression set (column storing expressions), the PARAMETERS clause in the CREATE INDEX statement cannot contain the STOREATTRS or INDEXATTRS clauses. In this case, the Expression Filter index is always created, using the parameters associated with the declared expression set. To find the parameters associated with the attribute set use the dbms_expfil.index_parameters and dbms_expfil.xpindex_parameters APIs and the user_expfil_index_parameters catalog view.

If the REPLACE DEFAULTS clause is not specified, the list of indexed attributes for an Expression Filter index is derived from the INDEXATTRS clause and the default index parameters associated with the corresponding attribute set. If this list is empty, the system picks a maximum of 10 stored attributes and indexes them.

If the PARAMETERS clause is not used with the CREATE INDEX statement and the index parameters are not assigned to the expression set, the default index parameters associated with the corresponding attribute set are used for the Expression Filter index. If the default index parameters list is empty, all the scalar attributes defined in the attribute set are stored and indexed in the predicate table.

Predicate statistics for the expression set must be available, in order to use the TOP clause for the corresponding Expression Filter index when the TOP clause is used for the STOREATTRS clause.

When an attribute is provided in the PARAMETERS clause as well as in the default index parameters, its stored indexed property is determined by the PARAMETERS clause specification.

Examples Using the CREATE INDEX Command for Expressions

To create an index, using the default index parameters specified for the expressions corresponding attribute set, use the following type of syntax for the CREATE INDEX command:

CREATE INDEX BoaterInterestIndex ON Boaters (Interest) INDEXTYPE IS EXFSYS.EXPFILTER;

An index can be created with a few additional stored attributes using the following statement.

CREATE INDEX BoaterInterestIndex ON Boaters (Interest) INDEXTYPE IS exfsys.ExpFilter

PARAMETERS ('STOREATTRS (SeaRating(Model, Year))

PREDSTORAGE (tablespace Boater_index_1) ');

If you wish to specify the complete list of stored and indexed attributes for an index, you would use a statement similar to the following example.

CREATE INDEX BoaterInterestIndex ON Boaters(Interest)

INDEXTYPE IS exfsys.ExpFilter

PARAMETERS ('REPLACE DEFAULTS

STOREATTRS (Model, SeaRating(Model, Year))

INDEXATTRS (Model, Year, Price)

PREDSTORAGE (tablespace tbs_1) ');

The TOP clause can be used in the parameters clause when statistics have been computed for the expression set. These statistics are accessible from the user_expfil_exprset_stats  view.

BEGIN

dbms_expfil.get_exprset_stats (expr_tab => 'Boaters',

expr_col => 'Interest');

END;

/

DROP INDEX InterestIndex;

CREATE INDEX BoaterInterestIndex ON Boaters (Interest)

INDEXTYPE IS exfsys.ExpFilter

PARAMETERS ('STOREATTRS TOP 4 INDEXATTRS TOP 3');

The Expression Filter uses predefined types, so let's take a quick look at them.

Expression Filter Object Type

The Expression Filter feature is supplied with a set of predefined types and public synonyms for these types. Most of these types are used for configuring index parameters with the Expression Filter procedural APIs. The exf$table_aliastype is used to support expressions defined on one or more database tables.

All the values and names passed to the types defined in this chapter are case-insensitive. In order to preserve the case, double quotation marks should be used around the values.

The next topic in this section on Expression Filters will discuss the dbms_expfil package, the heart of the Expression Filter feature.

The core of the Expression Filter feature is the dbms_expfil  package. This package contains the various procedures used to manage the Expression datatype and Expression data. The following table describes the procedures in the dbms_expfil package.

Expression Filter Views

The metadata associated with the Expression Filter feature can be viewed using the Expression Filter views. The Expression Filter views are defined with a xxx_EXPFIL prefix, where xxx can be USER or ALL. These views are read-only to the users and are created and maintained by the Expression Filter APIs.

The following Table lists the names of the views and their descriptions.

---

The above text is an excerpt from: Oracle SQL Tuning & CBO Internals ISBN 0-9745993-3-6 by Kimberly Floss