Summary of Expression Filter Subprograms

All the values and names passed to the procedures defined in the DBMS_EXPFIL package are not case sensitive, unless otherwise mentioned. To preserve the case, you use double quotation marks around the values.

Name of the elementary attribute to be added. No two attributes in a set can have the same name.

attr_type

Datatype of the attribute. This argument accepts any standard SQL datatype or the name of an object type that is accessible to the current user.

attr_defv1

Default value for the elementary attribute

tab_alias

The type that identifies the database table to which the attribute is aliased

Usage Notes

This procedure adds an elementary attribute to an attribute set. If the attribute set was originally created from an existing object type, then additional attributes cannot be added.

One or more, or all elementary attributes in an attribute set can be table aliases. If an elementary attribute is a table alias, then the value assigned to the elementary attribute is a ROWID from the corresponding table. An attribute set with one or more table alias attributes cannot be created from an existing object type. For more information about table aliases, see Appendix A in Oracle Database Application Developer's Guide - Rules Manager and Expression Filter.

Elementary attributes cannot be added to an attribute set that is already assigned to a column storing expressions.

The default value specification for an attribute is similar to a default value specification for a table column. The resulting default values should agree with the datatype of the attribute. For example, valid default values for an attribute of DATE datatype are SYSDATE and to_date('01-01-2004','DD-MM-YYYY').

Name of a function, package, or type (representing a function set) or its synonyms

Usage Notes

By default, an attribute set implicitly allows references to all Oracle supplied SQL functions for use by the expression set. If the expression set refers to a user-defined function, the function must be explicitly added to the attribute set.

The ADD_FUNCTIONS procedure adds a user-defined function or a package (or type) representing a set of functions to the attribute set. Any new or modified expressions are validated using this list. The function added to the attribute set, and thus used in the stored expressions, should not perform any DML or DDL (database state changing) operations. Any violations to this rule will only be caught at run-time while evaluating the expressions (this implies that this will not be checked during the ADD_FUNCTIONS procedure call).

The function or the package name can be specified with a schema extension. If a function name is specified without a schema extension, only such references in the expression set are considered valid. The expressions in a set can be restricted to use a synonym to a function or a package by adding the corresponding synonym to the attribute set. This preserves the portability of the expression set to other schemas.

Argument used to trust the existing expressions in a table (and skip validation)

Usage Notes

The ASSIGN_ATTRIBUTE_SET procedure assigns an attribute set to a VARCHAR2 column in a user table to create an Expression column. The attribute set contains the elementary attribute names and their datatypes and any functions used in the expressions. The attribute set is used by the Expression column to validate changes and additions to the expression set.

An attribute set can be assigned only to a table column in the same schema as the attribute set. An attribute set can be assigned to one or more table columns. Assigning an attribute set to a column storing expressions implicitly creates methods for the associated object type. For this operation to succeed, the object type cannot have any dependent objects before the attribute set is assigned.

By default, the column should not have any expressions at the time of association. However, if the values in the column are known to be valid expressions, you can use a value of 'TRUE' for the force argument to assign the attribute set to a column containing expressions.

BUILD_EXCEPTIONS_TABLE Procedure

This procedure creates the exception table, used in validation, in the current schema.

Syntax

DBMS_EXPFIL.BUILD_EXCEPTIONS_TABLE (
exception_tab IN VARCHAR2);

Parameters

Table 39-5 BUILD_EXCEPTIONS_TABLE Procedure Parameter

Parameter

Description

exception_tab

The name of the exception table

Usage Notes

The expressions stored in a table column can be validated using the VALIDATE_EXPRESSIONS procedure. During expression validation, you can optionally provide the name of the exception table in which the references to the invalid expressions are stored. The BUILD_EXCEPTIONS_TABLE procedure creates the exception table in the current schema.

A schema-extended name can be used for the from_set argument to copy an attribute set across schemas. The user issuing the command must have EXECUTE privileges for the object type associated with the original attribute set. The user must ensure that any references to schema objects (user-defined functions, tables, and embedded objects) are valid in the new schema.

The default index parameters and the user-defined function list of the new set can be changed independent of the original set.

YES, if the attributes for the attribute set should be derived from an existing object type

Usage Notes

The object type used for an attribute set cannot contain any user methods, and it should not be an evolved type (with the use of ALTER TYPE command). This object type should not have any dependent objects at the time of the attribute set creation. If the attribute set is not derived from an existing object type, this procedure creates an object type with a matching name.

DEFAULT_INDEX_PARAMETERS Procedure

This procedure assigns default index parameters to an attribute set. It also adds or drops a partial list of stored and indexed attributes to or from the default list associated with the attribute list.

An instance of EXF$XPATH_TAGS type with a partial list of XML elements and attributes to be configured for the Expression Filter index

operation

The operation to be performed on the list of index parameters. Default value: ADD. Valid values: ADD and DROP.

Usage Notes

The attribute set used for an expression set may have one or more XML type attributes (defined with XMLType datatype) and the corresponding expressions may contain XPath predicates on these attributes. The Expression Filter index created for the expression set can be tuned to process these XPath predicates efficiently by using some XPath-specific index parameters (in addition to some non-XPath index parameters).

The DEFAULT_XPINDEX_PARAMETERS procedure adds (or drops) a partial list of XPath parameters to the default index parameters associated with the attribute set. The XPath parameters are assigned to a specific XMLType attribute in the attribute set and this information can be viewed using the USER_EXPFIL_DEF_INDEX_PARAMS view. The DEFAULT_INDEX_PARAMETERS procedure and the DEFAULT_XPINDEX_PARAMETERS procedure can be used independent of each other. They maintain a common list of default index parameters for the attribute set.

DEFRAG_INDEX Procedure

This procedure rebuilds the bitmap indexes online and thus reduces the fragmentation.

Syntax

DBMS_EXPFIL.DEFRAG_INDEX (
idx_name IN VARCHAR2);

Parameters

Table 39-11 DEFRAG_INDEX Procedure Parameter

Parameter

Description

idx_name

The name of the Expression Filter index

Usage Notes

The bitmap indexes defined for the indexed attributes of an Expression Filter index become fragmented as additions and updates are made to the expression set. The DEFRAG_INDEX procedure rebuilds the bitmap indexes online and thus reduces the fragmentation.

Indexes can be defragmented when the expression set is being modified. However, you should schedule defragmentation when the workload is relatively light.

The following command is issued to defragment the bitmap indexes associated with the Expression Filter index:

BEGIN
DBMS_EXPFIL.DEFRAG_INDEX (idx_name => 'InterestIndex');
END;

DROP_ATTRIBUTE_SET Procedure

This procedure drops an attribute set not being used for any expression set.

Syntax

DBMS_EXPFIL.DROP_ATTRIBUTE_SET (
attr_set IN VARCHAR2);

Parameters

Table 39-12 DROP_ATTRIBUTE_SET Procedure Parameter

Parameter

Description

attr_set

The name of the attribute set to be dropped

Usage Notes

The DROP_ATTRIBUTE_SET procedure drops an attribute set not being used for any expression set. If the attribute set was initially created from an existing object type, the object type remains after dropping the attribute set. Otherwise, the object type is dropped with the attribute set.

When a representative set of expressions are stored in a table column, you can use predicate statistics for those expressions to configure the corresponding Expression Filter index (using the TOP parameters clause). The GET_EXPRSET_STATS procedure computes the predicate statistics for an expression set and stores them in the expression filter dictionary.

The SQL EVALUATE operator evaluates expressions with the privileges of the owner of the table that stores the expressions. The privileges of the user issuing the query are not considered. The owner of the table can insert, update, and delete expressions. Other users must have INSERT and UPDATE privileges for the table and INSERT EXPRESSION and UPDATE EXPRESSION privilege for a specific Expression column in the table.

Using the GRANT_PRIVILEGE procedure, the owner of the table can grant INSERTEXPRESSION or UPDATEEXPRESSION privileges on one or more Expression columns to other users. Both the privileges can be granted to a user by specifying ALL for the privilege type.

The owner of Consumer table can grant INSERTEXPRESSION privileges to user SCOTT with the following command. User SCOTT should also have INSERT privileges on the table so that he can add new expressions to the set.

An instance of EXF$ATTRIBUTE_LIST with a partial list of stored and indexed attributes

operation

The operation to be performed on the list of index parameters. Default value: ADD. Valid values: ADD and DROP.

Usage Notes

An attribute set can be used by multiple expression sets stored in different columns of user tables. By default, the index parameters associated with the attribute set are used to define an Expression Filter index on an expression set. If you need to fine-tune the index for each expression set, you can specify a small list of the index parameters in the PARAMETERS clause of the CREATEINDEX statement. However, when an Expression Filter index uses a large number of index parameters or if the index is configured for XPath predicates, fine-tuning the parameters with the CREATEINDEX statement is not possible.

The INDEX_PARAMETERS procedure fine-tunes the index parameters for each expression set before index creation. This procedure can be used to copy the defaults from the corresponding attribute set and selectively add (or drop) additional index parameters for the expression set. (You use the XPINDEX_PARAMETERS procedure to add and drop XPath index parameters.) The Expression Filter index defined for an expression set with a non-empty list of index parameters always uses these parameters. The INDEX_PARAMETERS procedure cannot be used when the Expression Filter index is already defined for the column storing expressions.

The operations allowed with this procedure include:

Deriving the current list of default index parameters (including any XPath-specific parameters) from the corresponding attribute set and assigning them to the specified expression set (a value of DEFAULT for the operation argument).

Adding (or dropping) one or more attributes to (or from) the current list of parameters assigned to the expression set (values of ADD or DROP for the operation argument).

Clearing the index parameters assigned to the expression set. This enables the user to start using default parameters or tune the parameters from scratch (a value of CLEAR for the operation argument).

Note:

This procedure is useful only when an attribute set is shared across multiple expression sets. In all other cases, the defaults assigned to the attribute set can be tuned for the expression set using it.

The new list of operators that are frequently used in the predicates with the attribute

Usage Notes

The MODIFY_OPERATOR_LIST procedure modifies the operator list for the stored and indexed attributes defined in the attribute set's default index parameters. Existing Expression Filter indexes are not affected when an attribute's operator list is modified. The updated index defaults are used when a new Expression Filter index is created or when an existing index is rebuilt.

A column of an expression datatype can be converted back to a VARCHAR2 type by unassigning the attribute set. You can unassign an attribute set from a column storing expressions if an Expression Filter index is not defined on the column.

The name of the exception table. This table is created using the BUILD_EXCEPTIONS_TABLE procedure.

Usage Notes

The expressions stored in a table may have references to schema objects like user-defined functions and tables. When these schema objects are dropped or modified, the expressions could become invalid and the subsequent evaluation (query with EVALUATE operator) could fail.

The VALIDATE_EXPRESSIONS procedure validates all the expressions in a set. By default, the expression validation utility fails on the first expression that is invalid. Optionally, the caller can pass an exception table to store references to all the invalid expressions. In addition to validating expressions in the set, this procedure validates the parameters (stored and indexed attributes) of the associated index and the approved list of user-defined functions. Any errors in the index parameters or the user-defined function list are immediately reported to the caller.

An instance of EXF$XPATH_TAGS type with a partial list of XML elements and attributes

operation

The operation to be performed on the list of index parameters. Default value: ADD. Valid values: ADD and DROP.

Usage Notes

When an attribute set is shared by multiple expression sets, the INDEX_PARAMETERS procedure can be used to tune the simple (non-XPath) index parameters for each expression set. The XPINDEX_PARAMETERS procedure is used in conjunction with the INDEX_PARAMETERS procedure to fine-tune the XPath-specific index parameters for each expression set.