SQL Expression Filter Tips

Oracle Tips by Burleson

Oracle10g 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:

The EXFSYS schema that determines the storage, syntax, and semantics of the Expression datatypes.
The Expression Filter indexing mechanism.
A set of operators and functions that evaluate the Expressions stored in user tables for a given data item.
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:

Creates the exfsys schema owner using exfsys.exe
Loads information about the exfsys schema into the dbms_registry package
Creates the Java Expresssion library, using initexf.sql
Creates the exfsys objects, using exftyp.sql
Creates the Expression Filter Dictionary, using exftab.sql
Creates the public PL/SQL packages with exfpbs.sql
Creates the Expression filter catalog views, using exfview.sql
Creates the sys owned private package, using exfsppvs.plb
Creates the Expression filter APIs, using exfeapvs.plb
Creates the Expression filter indextype and operators, using exfimpvs.plb
Creates the Xpath Expression filter support, using exfxppvs.plb
Creates the indextype definition for the EXPFilter indextype
Associates the statistical methods with the appropriate functions

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_alias type 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.

PROCEDURE	DESCRIPTION
add_elementary_attribute	Adds a specified attribute to the attribute set.
add_functions	Adds a Function, Type, or Package to the approved list of objects within an attribute set
assign_attribute_set	Assigns an attribute set to the specified table column used for storing expressions.
build_exception_table	Creates an exception table to hold references to invalid expressions.
clear_exprset_stats	Clears the predicate statistics for an expression set.
copy_attribute_set	Makes a copy of the attribute set.
create_attribute_set	Creates an attribute set.
default_index_parameters	Assigns default index parameters to an attribute set.
default_xpindex_parameters	Assigns default XPath index parameters to an attribute set.
defrag_index	Rebuilds the bitmap indexes online to reduce fragmentation.
drop_attribute_set	Drops an unused attribute set.
get_exprset_stats	Collects predicate statistics for an expression set.
grant_privilege	Grants an expression DML privilege to a user.
index_parameters	Assigns index parameters to an expression set.
revoke_privilege	Revokes an expression DML privilege from a user.
unassign_attribute_set	Breaks the association between a column storing expressions and the attribute set.
validate_expressions	Validates expression metadata and the expressions stored in a column.
xpindex_parameters	Assigns XPath index parameters to an expression set.

Table 6.3 dbms_expfil Procedures

We have mentioned several views associated with Expressions, let's take a quick look at them as the final topic in this section

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.

VIEW NAME	DESCRIPTION
user_expfil_aset_functions	List of functions/packages approved for the attribute set.
user_expfil_attributes	List of elementary attributes of the attribute set.
user_expfil_attribute_sets	List of attribute set.
user_expfil_def_index_params	List of default index parameters.
user_expfil_expression_sets	List of expression sets.
user_expfil_exprset_stats	List of predicate statistics for the expression sets.
user_expfil_index_params	List of index parameters assigned to the expression set.
user_expfil_indexes	List of expression filter indexes.
user_expfil_predtab_attributes	List of stored and indexed attributes for the indexes.
user_expfil_privileges	List of all the expression privileges of the current user.

Table 6.4 Expression Filter Views

Next, we will look at the new features in SQL Regular expressions.