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

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

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

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.

 

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 -2016 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