Oracle Consulting Oracle Training Oracle Support Development
Oracle Books
SQL Server Books
IT Books
Job Interview Books
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle


Privacy Policy



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

   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.




Adds a specified attribute to the attribute set.


Adds a Function, Type, or Package to the approved list of objects within an attribute set


Assigns an attribute set to the specified table column used for storing expressions.


Creates an exception table to hold references to invalid expressions.


Clears the predicate statistics for an expression set.


Makes a copy of the attribute set.


Creates an attribute set.


Assigns default index parameters to an attribute set.


Assigns default XPath index parameters to an attribute set.


Rebuilds the bitmap indexes online to reduce fragmentation.


Drops an unused attribute set.


Collects predicate statistics for an expression set.


Grants an expression DML privilege to a user.


Assigns index parameters to an expression set.


Revokes an expression DML privilege from a user.


Breaks the association between a column storing expressions and the attribute set.


Validates expression metadata and the expressions stored in a column.


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.




List of functions/packages approved for the attribute set.


List of elementary attributes of the attribute set.


List of attribute set.


List of default index parameters.


List of expression sets.


List of predicate statistics for the expression sets.


List of index parameters assigned to the expression set.


List of expression filter indexes.


List of stored and indexed attributes for the indexes.


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.


Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster

Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   



 Copyright © 1996 -2017 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks