8 Management Procedures Using the DBMS_EXPFIL Package

The Expression Filter DBMS_EXPFIL package contains all the procedures used to manage attribute sets, expression sets, expression indexes, optimizer statistics, and privileges. Table 8-1 describes the procedures in the DBMS_EXPFIL package. These procedures are further described in this chapter.

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.

Table 8-1 DBMS_EXPFIL Procedures

Procedure	Description
ADD_ELEMENTARY_ATTRIBUTE	Adds the specified attribute to the attribute set.
ADD_FUNCTIONS	Adds a function, type, or package to the approved list of functions with an attribute set.
ASSIGN_ATTRIBUTE_SET	Assigns an attribute set to a column storing expressions.
BUILD_EXCEPTIONS_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.

ADD_ELEMENTARY_ATTRIBUTE Procedure

This procedure adds the specified attribute to the attribute set.

Format

procedure ADD_ELEMENTARY_ATTRIBUTE (
              attr_set   IN   VARCHAR2,         --- attr set name
              attr_name  IN   VARCHAR2,         --- attr name
              attr_type  IN   VARCHAR2);        --- attr type
 
--- or
 
procedure ADD_ELEMENTARY_ATTRIBUTE (
              attr_set   IN   VARCHAR2,         --- attr set name
              attr_name  IN   VARCHAR2,         --- table alias (name)
              tab_alias  IN   exf$table_alias); --- table alias for

Arguments

attr_set: Name of the attribute set to which this attribute is added.
attr_name: 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.
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.

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

See "Defining Attribute Sets" for more information about adding elementary attributes.

Related views: USER_EXPFIL_ATTRIBUTE_SETS and USER_EXPFIL_ATTRIBUTES.

Examples

The following commands add two elementary attributes to an attribute set:

BEGIN
  DBMS_EXPFIL.ADD_ELEMENTARY_ATTRIBUTE (
                               attr_set  => 'HRAttrSet',
                               attr_name => 'HRREP',
                               attr_type => 'VARCHAR2(30)');
  DBMS_EXPFIL.ADD_ELEMENTARY_ATTRIBUTE (
                               attr_set  => 'HRAttrSet',
                               attr_name => 'DEPT',
                               tab_alias => exf$table_alias('DEPT'));
END;
/

ADD_FUNCTIONS Procedure

This procedure adds a user-defined function, package, or type representing a set of functions to the attribute set.

Format

PROCEDURE ADD_FUNCTIONS (
              attr_set   IN   VARCHAR2,    --- attr set name 
              funcs_name IN   VARCHAR2);   --- function/package/type name

Arguments

attr_set: Name of the attribute set to which the functions are added.
funcs_name: 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 expression set 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 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.

See "Defining Attribute Sets" for more information about adding functions to an attribute set.

Related views: USER_EXPFIL_ATTRIBUTE_SETS and USER_EXPFIL_ASET_FUNCTIONS

Examples

The following commands add two functions to the attribute set:

BEGIN 
  DBMS_EXPFIL.ADD_FUNCTIONS (attr_set   => 'Car4Sale', 
                             funcs_name => 'HorsePower');
  DBMS_EXPFIL.ADD_FUNCTIONS (attr_set   => 'Car4Sale', 
                             funcs_name => 'Scott.CrashTestRating');
END;
/

ASSIGN_ATTRIBUTE_SET Procedure

This procedure assigns an attribute set to a VARCHAR2 column in a user table to create an Expression column.

Format

PROCEDURE ASSIGN_ATTRIBUTE_SET (
              attr_set   IN   VARCHAR2,    --- attr set name
              expr_tab   IN   VARCHAR2,    --- name of the table
              expr_col   IN   VARCHAR2,    --- exp column in the table 
              force      IN   VARCHAR2     --- to use existing expressions
                         default 'FALSE');

Arguments

attr_set: The name of the attribute set.
expr_tab: The table storing the expression set.
expr_col: The column in the table that stores the expressions.
force: 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.

See "Defining Expression Columns" for more information about adding elementary attributes.

Related views: USER_EXPFIL_ATTRIBUTE_SETS and USER_EXPFIL_EXPRESSION_SETS

Examples

The following command assigns the attribute set to a column storing expressions. The expression set should be empty at the time of association.

BEGIN
  DBMS_EXPFIL.ASSIGN_ATTRIBUTE_SET (attr_set => 'Car4Sale', 
                                    expr_tab => 'Consumer',
                                    expr_col => 'Interest');
END;
/

BUILD_EXCEPTIONS_TABLE Procedure

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

Format

PROCEDURE BUILD_EXCEPTIONS_TABLE (
              exception_tab IN VARCHAR2);  -- exception table to be created --

Arguments

exception_tab: 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.

See "Evaluation Semantics" and "VALIDATE_EXPRESSIONS Procedure" for more information.

Related view: USER_TABLES

Examples

The following command creates the exception table, InterestExceptions, in the current schema:

BEGIN
  DBMS_EXPFIL.BUILD_EXCEPTIONS_TABLE (
                              exception_tab => 'InterestExceptions');
END;
/

CLEAR_EXPRSET_STATS Procedure

This procedure clears the predicate statistics for the expression set stored in a table column.

Format

PROCEDURE CLEAR_EXPRSET_STATS (
              expr_tab   IN   VARCHAR2,    --- table storing expression set
              expr_col   IN   VARCHAR2);   --- column in the table with set

Arguments

exp_tab: The table storing the expression set.
expr_col: The column in the table that stores the expressions.

Usage Notes

This procedure clears the predicate statistics for the expression set stored in a table column. See also "GET_EXPRSET_STATS Procedure" for information about gathering the statistics.

Related views: USER_EXPFIL_EXPRESSION_SETS and USER_EXPFIL_EXPRSET_STATS

Examples

The following command clears the predicate statistics for the expression set stored in Interest column of the Consumer table:

BEGIN
  DBMS_EXPFIL.CLEAR_EXPRSET_STATS (expr_tab => 'Consumer',
                                    expr_col => 'Interest');
END;
/

COPY_ATTRIBUTE_SET Procedure

This procedure copies an attribute set along with its user-defined function list and default index parameters to another set.

Format

PROCEDURE COPY_ATTRIBUTE_SET (
             from_set   IN   VARCHAR2,    --- name of an existing att set
             to_set     IN   VARCHAR2);   --- new set name

Arguments

from_set: Name of an existing attribute set to be copied.
to_set: Name of the new attribute set.

Usage Notes

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.

Related views: ALL_EXPFIL_ATTRIBUTE_SETS and ALL_EXPFIL_ATTRIBUTES.

Examples

The following command makes a copy of the Car4Sale attribute set:

BEGIN
  DBMS_EXPFIL.COPY_ATTRIBUTE_SET (from_set => 'Car4Sale', 
                                  to_set   => 'Vehicle');
END;
/

CREATE_ATTRIBUTE_SET Procedure

This procedure creates an empty attribute set or an attribute set with a complete set of elementary attributes derived from an object type with a matching name.

Format

PROCEDURE CREATE_ATTRIBUTE_SEt (
              attr_set   IN   VARCHAR2,    --- attr set name
              from_type  IN   VARCHAR2     --- object type for attributes
                         default 'NO');

Arguments

attr_set: The name of the attribute set to be created.
from_type: 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.

An attribute set with one or more table alias attributes cannot be derived from an object type. For this purpose, create an empty attribute set and add one elementary attribute at a time using the DBMS_EXPFIL.ADD_ELEMENTARY_ATTRIBUTE procedure. (See Appendix A for more information.)

See "Defining Attribute Sets" and "ADD_ELEMENTARY_ATTRIBUTE Procedure" for more information.

Related views: USER_EXPFIL_ATTRIBUTE_SET and USER_EXPFIL_ATTRIBUTES.

Examples

The following commands create an attribute set with all the required elementary attributes derived from the Car4Sale type:

CREATE OR REPLACE TYPE Car4Sale AS OBJECT 
                                   (Model   VARCHAR2(20), 
                                    Year    NUMBER, 
                                    Price   NUMBER, 
                                    Mileage NUMBER);
/
 
 
BEGIN 
  DBMS_EXPFIL.CREATE_ATTRIBUTE_SET(attr_set  => 'Car4Sale',
                                   from_type => 'YES');
END;
/

Assuming that the Car4Sale type does not exist, the attribute set can be created from scratch as shown in the following example:

BEGIN
  DBMS_EXPFIL.CREATE_ATTRIBUTE_SET(attr_set => 'Car4Sale');
  DBMS_EXPFIL.ADD_ELEMENTARY_ATTRIBUTE( 
                               attr_set  => 'Car4Sale',
                               attr_name => 'Model',
                               attr_type => 'VARCHAR2(20)');
  DBMS_EXPFIL.ADD_ELEMENTARY_ATTRIBUTE(
                               attr_set  => 'Car4Sale',
                               attr_name => 'Year',
                               attr_type => 'NUMBER');
  DBMS_EXPFIL.ADD_ELEMENTARY_ATTRIBUTE(
                               attr_set  => 'Car4Sale',
                               attr_name => 'Price',
                               attr_type => 'NUMBER');
  DBMS_EXPFIL.ADD_ELEMENTARY_ATTRIBUTE(
                               attr_set  => 'Car4Sale',
                               attr_name => 'Mileage',
                               attr_type => 'NUMBER');
END;
/

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.

Format

PROCEDURE DEFAULT_INDEX_PARAMETERS (
              attr_set   IN   VARCHAR2,    --- attribute set name
              attr_list  IN   EXF$ATTRIBUTE_LIST,
                                           --- stored and indexed attributes
              operation  IN   VARCHAR2     --- to ADD or DROP
                               default 'ADD');

Arguments

attr_set: The name of the attribute set.
attr_list: An instance of EXF$ATTRIBUTE_LIST with a partial list of (default) stored and indexed attributes for an 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

Existing Expression Filter indexes are not modified when the default parameters for the corresponding attribute set are changed. The new index defaults are used when a new Expression Filter index is created and when an existing index is rebuilt. (See ALTER INDEX REBUILD in Chapter 6 for more information about rebuilding indexes.)

See "Creating an Index from Default Parameters" for more information about assigning default index parameters to an attribute set.

Related views: USER_EXPFIL_ATTRIBUTE_SETS and USER_EXPFIL_DEF_INDEX_PARAMS

Examples

The following command adds the specified stored and indexed attributes to the attribute set's default index parameters list:

BEGIN
 DBMS_EXPFIL.DEFAULT_INDEX_PARAMETERs(
    attr_set  => 'Car4Sale', 
    attr_list => exf$attribute_list (
       exf$attribute (attr_name => 'Model',            
                      attr_oper => exf$indexoper('='),
                      attr_indexed => 'TRUE'),         
       exf$attribute (attr_name => 'Price', 
                      attr_oper => exf$indexoper('all'), 
                      attr_indexed => 'TRUE'), 
       exf$attribute (attr_name => 'HorsePower(Model, Year)', 
                      attr_oper => exf$indexoper('=','<','>','>=','<='), 
                      attr_indexed => 'FALSE'),
       exf$attribute (attr_name => 'CrashTestRating(Model, Year)', 
                      attr_oper => exf$indexoper('=','<','>','>=','<='), 
                      attr_indexed => 'FALSE')),
    operation => 'ADD');
END;
/

The following command drops the CrashTestRating(Model, Year) attribute (stored or indexed) from the previous list.

BEGIN
  DBMS_EXPFIL.DEFAULT_INDEX_PARAMETERS(
      attr_set  => 'Car4Sale', 
      attr_list => exf$attribute_list (
         exf$attribute (attr_name => 'CrashTestRating(Model, Year)')),
      operation => 'DROP');
END;
/

DEFAULT_XPINDEX_PARAMETERS Procedure

This procedure adds (or drops) a partial list of XPath parameters to the default index parameters associated with the attribute set.

Format

PROCEDURE DEFAULT_XPINDEX_PARAMETERS (
              attr_set   IN   VARCHAR2,    --- attribute set name
              xmlt_attr  IN   VARCHAR2,    --- XMLType attrubue name
              xptag_list IN   EXF$XPATH_TAGS,
                                           --- XPath tags for index
              operation  IN   VARCHAR2     --- to ADD or DROP
                               default 'ADD');

Arguments

attr_set: The name of the attribute set.
xmlt_attr: The name of the attribute with the XMLType datatype.
xptag_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.

See "Index Tuning for XPath Predicates" for more information about XPath parameters to the default index parameters of an attribute set. See also "DEFAULT_INDEX_PARAMETERS Procedure" for more information about default index parameters.

Related views: USER_EXPFIL_ATTRIBUTES and USER_EXPFIL_DEF_INDEX_PARAMS.

Note:

The values assigned to the tag_name argument of exf$xpath_tag type are case sensitive.

Examples

The following command adds the specified XML tags to the default index parameters list along with their preferences such as positional or value filter and indexed or stored predicate group:

BEGIN
  DBMS_EXPFIL.DEFAULT_XPINDEX_PARAMETERS(
       attr_set   => 'Car4Sale',
       xmlt_attr  => 'Details',
       xptag_list =>                                    --- XPath tag list 
         exf$xpath_tags(
           exf$xpath_tag(tag_name    => 'stereo@make',  --- XML attribute
                         tag_indexed => 'TRUE',
                         tag_type    => 'VARCHAR(15)'), --- value filter
           exf$xpath_tag(tag_name    => 'stereo',       --- XML element
                         tag_indexed => 'FALSE',
                         tag_type    => null),          --- positional filter
           exf$xpath_tag(tag_name    => 'memory',       --- XML element
                         tag_indexed => 'TRUE',
                         tag_type    => 'VARCHAR(10)'), --- value filter
           exf$xpath_tag(tag_name    => 'GPS',
                         tag_indexed => 'TRUE',
                         tag_type    => null)
          )
        );
END;
/

The following command drops the stereo@make tag from the default index parameters:

BEGIN
  DBMS_EXPFIL.DEFAULT_XPINDEX_PARAMETERS(
       attr_set   => 'Car4Sale',
       xmlt_attr  => 'Details',
       xptag_list =>                                    --- XPath tag list 
         exf$xpath_tags(
           exf$xpath_tag(tag_name    => 'stereo@make')
         ),
       operation => 'DROP'
       );
END;
/

DEFRAG_INDEX Procedure

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

Format

PROCEDURE DEFRAG_INDEX (
              idx_name   IN  VARCHAR2);    --- expfil index to defrag

Arguments

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.

See "Index Storage and Maintenance" for more information about rebuilding indexes.

Related views: USER_EXPFIL_INDEXES and USER_INDEXES.

Examples

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.

Format

PROCEDURE DROP_ATTRIBUTE_SET (
              attr_set   IN   VARCHAR2);   --- attr set name

Arguments

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.

Related views: USER_EXPFIL_ATTRIBUTE_SETS and USER_EXPFIL_EXPRESSION_SETS.

Examples

Assuming that the attribute set is not used by an Expression column, the following command drops the attribute set:

BEGIN
  DBMS_EXPFIL.DROP_ATTRIBUTE_SET(attr_set => 'Car4Sale');  
END;
/

GET_EXPRSET_STATS Procedure

This procedure computes the predicate statistics for an expression set and stores them in the expression filter dictionary.

Format

PROCEDURE GET_EXPRSET_STATS (
              expr_tab   IN   VARCHAR2,    --- table storing expression set
              expr_col   IN   VARCHAR2);   --- column in the table with set

Arguments

expr_tab: The table storing the expression set.
expr_col: The column in the table that stores the expressions.

Usage Notes

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.

See "Creating an Index from Statistics" for more information about using predicate statistics.

Related views: USER_EXPFIL_EXPRESSION_SETS and USER_EXPFIL_EXPRSET_STATS.

Examples

The following command computes the predicate statistics for the expressions stored in the Interest column of the Consumer table:

BEGIN
  DBMS_EXPFIL.GET_EXPRSET_STATS (expr_tab => 'Consumer',
                                 expr_col => 'Interest');
END;
/

GRANT_PRIVILEGE Procedure

This procedure grants privileges on one or more Expression columns to other users.

Format

PROCEDURE GRANT_PRIVILEGE (
              expr_tab   IN  VARCHAR2,     --- table w/ the expr column
              expr_col   IN  VARCHAR2,     --- column storing the expressions
              priv_type  IN  VARCHAR2,     --- type of priv to be granted
              to_user    IN  VARCHAR2);    --- user to which the priv is
                                           ---   granted

Arguments

expr_tab: The table storing the expression set.
expr_col: The column in the table that stores the expressions.
priv_type: Types of the privilege to be granted. Valid values: INSERT EXPRESSION, UPDATE EXPRESSION, ALL
to_user: User to which the privilege is granted.

Usage Notes

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 INSERT EXPRESSION or UPDATE EXPRESSION 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.

See "REVOKE_PRIVILEGE Procedure" and "Granting and Revoking Privileges" for more information about granting and revoking privileges.

Related views: USER_EXPFIL_EXPRESSION_SETS and USER_EXPFIL_PRIVILEGES.

Examples

The owner of Consumer table can grant INSERT EXPRESSION 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.

BEGIN
  DBMS_EXPFIL.GRANT_PRIVILEGE (expr_tab  => 'Consumer',
                               expr_col  => 'Interest',
                               priv_type => 'INSERT EXPRESSION',
                               to_user   => 'SCOTT');
END;
/

INDEX_PARAMETERS Procedure

This procedure fine-tunes the index parameters for each expression set before index creation.

Format

PROCEDURE INDEX_PARAMETERS (
              expr_tab   IN   VARCHAR2,    --- table with expr column
              expr_col   IN   VARCHAR2,    --- column storing expressions
              attr_list  IN   EXF$ATTRIBUTE_LIST,
              operation  IN   VARCHAR2     --- type of operation 
                               default 'ADD');

Arguments

expr_tab: The table storing the expression set.
expr_col: The column in the table that stores the expressions.
attr_list: 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, DROP, DEFAULT, and CLEAR.

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 CREATE INDEX 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 CREATE INDEX 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.

See "Creating an Index from Exact Parameters" and "XPINDEX_PARAMETERS Procedure" for more information.

Related views: USER_EXPFIL_EXPRESSION_SETS, USER_EXPFIL_DEF_INDEX_PARAMETERS and USER_EXPFIL_INDEX_PARAMETERS.

Examples

The following command synchronizes the expression set's index parameters with the defaults associated with the corresponding attribute set:

BEGIN
  DBMS_EXPFIL.INDEX_PARAMETERS(expr_tab  => 'Consumer',
                               expr_col  => 'Interest',
                               attr_list => null, 
                               operation => 'DEFAULT');
END;
/

The following command adds a stored attribute to the expression set's index parameters.

BEGIN
  DBMS_EXPFIL.INDEX_PARAMETERS(expr_tab  => 'Consumer',
                               expr_col  => 'Interest',
                               attr_list =>
                                exf$attribute_list (
                                 exf$attribute (
                                   attr_name => 'CrashTestRating(Model, Year)',
                                   attr_oper => exf$indexoper('all'),
                                   attr_indexed => 'FALSE')),
                               operation => 'ADD');
END;
/

The following command clears the index parameters associated with the expression set:

BEGIN
  DBMS_EXPFIL.INDEX_PARAMETERS(expr_tab  => 'Consumer',
                               expr_col  => 'Interest',
                               attr_list => null, 
                               operation => 'CLEAR');
END;
/

A subsequent index creation will use the default index parameters assigned to the corresponding attribute set.

REVOKE_PRIVILEGE Procedure

This procedure revokes an expression privilege previously granted by the owner.

Format

PROCEDURE REVOKE_PRIVILEGE (
              expr_tab   IN  VARCHAR2,     --- table with the expr column
              expr_col   IN  VARCHAR2,     --- column storing the expression
              priv_type  IN  VARCHAR2,     --- type of privilege to be revoked
              from_user  IN  VARCHAR2);    --- user from which the priv is
                                           ---   revoked

Arguments

expr_tab: The table storing the expression set.
expr_col: The column in the table that stores the expressions.
priv_type: Type of privilege to be revoked.
from_user: User from which the privilege is revoked.

Usage Notes

The REVOKE_PRIVILEGE procedure revokes an expression privilege previously granted by the owner.

See "GRANT_PRIVILEGE Procedure" and "Granting and Revoking Privileges" for more information about granting and revoking privileges.

Related views: USER_EXPFIL_EXPRESSION_SETS and USER_EXPFIL_PRIVILEGES.

Examples

The following command revokes the INSERT EXPRESSION privilege on the Interest column of the Consumer table from user SCOTT:

BEGIN
  DBMS_EXPFIL.REVOKE_PRIVILEGE (expr_tab  => 'Consumer',
                                expr_col  => 'Interest',
                                priv_type => 'INSERT EXPRESSION',
                                 from_user => 'SCOTT');
END;/

UNASSIGN_ATTRIBUTE_SET Procedure

This procedure unassigns an attribute set from a column storing expressions.

Format

PROCEDURE UNASSIGN_ATTRIBUTE_SET (
              expr_tab   IN   VARCHAR2,    --- table with expr. column
              expr_col   IN   VARCHAR2);   --- column storing expr. set

Arguments

expr_tab: The table storing the expression set.
expr_col: The column in the table that stores the expressions.

Usage Notes

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.

See "ASSIGN_ATTRIBUTE_SET Procedure" for information about assigning attribute sets.

Related views: USER_EXPFIL_EXPRESSION_SETS and USER_EXPFIL_INDEXES.

Examples

The following command unassigns the attribute set previously assigned to the Interest column of the Consumer table. (See Section 5.1.)

BEGIN
  DBMS_EXPFIL.UNASSIGN_ATTRIBUTE_SET (expr_tab => 'Consumer',
                                      expr_col => 'Interest');
END;
/

VALIDATE_EXPRESSIONS Procedure

This procedure validates all the expressions in a set.

Format

PROCEDURE VALIDATE_EXPRESSIONS (
              expr_tab      IN  VARCHAR2,  --- expressions table 
              expr_col      IN  VARCHAR2,  --- column storing expressions
              exception_tab IN  VARCHAR2   --- exception table
                    default null);

Arguments

expr_tab: The table storing the expression set.
expr_col: The column in the table that stores the expressions.
exception_tab: 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.

See "Evaluation Semantics" and "BUILD_EXCEPTIONS_TABLE Procedure" for more information.

Related views: USER_EXPFIL_EXPRESSION_SETS, USER_EXPFIL_ASET_FUNCTIONS, and USER_EXPFIL_PREDTAB_ATTRIBUTES.

Examples

The following command validates the expressions stored in the Interest column of the Consumer table.

BEGIN
  DBMS_EXPFIL.VALIDATE_EXPRESSIONS (expr_tab => 'Consumer',
                                    expr_col => 'Interest');
END;
/

XPINDEX_PARAMETERS Procedure

This procedure is used in conjunction with the INDEX_PARAMETERS procedure to fine-tune the XPath-specific index parameters for each expression set.

Format

PROCEDURE XPINDEX_PARAMETERS (
              expr_tab   IN   VARCHAR2,    --- table with expr column
              expr_col   IN   VARCHAR2,    --- column storing expressions
              xmlt_attr  IN   VARCHAR2,    --- XMLType attrubue name
              xptag_list IN   EXF$XPATH_TAGS,
              operation  IN   VARCHAR2     --- to ADD or DROP
                               default 'ADD');

Arguments

expr_tab: The table storing the expression set.
expr_col: The column in the table that stores the expressions.
xmlt_attr: The name of the attribute with the XMLType datatype.
xptag_list: 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 or 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.

See also "INDEX_PARAMETERS Procedure" and "Index Tuning for XPath Predicates" for more information.

Related views: USER_EXPFIL_ATTRIBUTES, USER_EXPFIL_DEF_INDEX_PARAMS, and USER_EXPFIL_INDEX_PARAMS.

Note:

The values assigned to the tag_name argument of exf$xpath_tag type are case-sensitive.

Examples

The following command synchronizes the expression set's index parameters (XPath and non-XPath) with the defaults associated with the corresponding attribute set:

BEGIN
  DBMS_EXPFIL.INDEX_PARAMETERS(expr_tab  => 'Consumer',
                               expr_col  => 'Interest',
                               attr_list => null, 
                               operation => 'DEFAULT');
END;
/

The following command adds an XPath-specific index parameter to the expression set:

BEGIN
  DBMS_EXPFIL.XPINDEX_PARAMETERS(expr_tab   => 'Consumer',
                                 expr_col   => 'Interest',
                                 xmlt_attr  => 'Details',
                                 xptag_list =>
                                  exf$xpath_tags(
                                   exf$xpath_tag(tag_name    => 'GPS',
                                                 tag_indexed => 'TRUE',
                                                 tag_type    => null)),
                                 operation  => 'ADD');
END;
/