The DBMS_EXPFIL
package contains all the procedures used to manage attribute sets, expression sets, expression indexes, optimizer statistics, and privileges by Expression Filter.
See Also:
Oracle Database Application Developer's Guide - Rules Manager and Expression Filter for more information.This chapter contains the following topics:
Table 39-1 describes the subprograms in the DBMS_EXPFIL
package.
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 39-1 DBMS_EXPFIL Package Subprograms
Subprogram | Description |
---|---|
Adds the specified attribute to the attribute set |
|
Adds a function, type, or package to the approved list of functions with an attribute set |
|
Assigns an attribute set to a column 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 |
|
Modifies the list of common operators used in predicates with a certain attribute |
|
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 |
This procedure adds the specified attribute to the attribute set. The procedure is overloaded. The different functionality of each form of syntax is presented along with the definitions.
Adds the specified elementary attribute to the attribute set:
DBMS_EXPFIL.ADD_ELEMENTARY_ATTRIBUTE ( attr_set IN VARCHAR2, attr_name IN VARCHAR2, attr_type IN VARCHAR2, attr_defv1 IN VARCHAR2 DEFAULT NULL);
Identifies the elementary attributes that are table aliases and adds them to the attribute set:
DBMS_EXPFIL.ADD_ELEMENTARY_ATTRIBUTE ( attr_set IN VARCHAR2, attr_name IN VARCHAR2, tab_alias IN exf$table_alias);
Table 39-2 ADD_ELEMENTARY_ATTRIBUTE Procedure Parameters
Parameter | Description |
---|---|
|
Name of the attribute set to which this attribute is added |
|
Name of the elementary attribute to be added. No two attributes in a set can have the same name. |
|
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. |
|
Default value for the elementary attribute |
|
The type that identifies the database table to which the attribute is aliased |
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')
.
See "Defining Attribute Sets" in Oracle Database Application Developer's Guide - Rules Manager and Expression Filter for more information about adding elementary attributes.
Related views: USER_EXPFIL_ATTRIBUTE_SETS
and USER_EXPFIL_ATTRIBUTES
.
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)' attr_defv1 => 'Betty Smith'); DBMS_EXPFIL.ADD_ELEMENTARY_ATTRIBUTE ( attr_set => 'HRAttrSet', attr_name => 'DEPT', tab_alias => exf$table_alias('DEPT')); END;
The following commands define a CreationTime elementary attribute that takes the database time as the default value.
BEGIN DBMS_EXPFIL.ADD_ELEMENTARY_ATTRIBUTE ( attr_set => 'PurchaseOrder', attr_name => 'CreationTime', attr_type => 'DATE', attr_defvl => 'SYSDATE'); END;
Alternately, the following commands initialize the CreationTime attribute to a specific value when it is not explicitly specified in the data item passed to the EVALUATE operator.
BEGIN DBMS_EXPFIL.ADD_ELEMENTARY_ATTRIBUTE ( attr_set => 'PurchaseOrder', attr_name => 'CreationTime', attr_type => 'DATE', attr_defvl => 'to_date(''01-01-2004'',''DD-MM-YYYY'')'); END;
This procedure adds a user-defined function, package, or type representing a set of functions to the attribute set.
DBMS_EXPFIL.ADD_FUNCTIONS ( attr_set IN VARCHAR2, funcs_name IN VARCHAR2);
Table 39-3 ADD_FUNCTIONS Procedure Parameters
Parameter | Description |
---|---|
|
Name of the attribute set to which the functions are added |
|
Name of a function, package, or type (representing a function set) or its synonyms |
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.
See "Defining Attribute Sets"in Oracle Database Application Developer's Guide - Rules Manager and Expression Filter for more information about adding functions to an attribute set.
Related views: USER_EXPFIL_ATTRIBUTE_SETS
and USER_EXPFIL_EXPRESSION_SETS
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;
This procedure assigns an attribute set to a VARCHAR2
column in a user table to create an Expression column.
DBMS_EXPFIL.ASSIGN_ATTRIBUTE_SET ( attr_set IN VARCHAR2, expr_tab IN VARCHAR2, expr_col IN VARCHAR2, force IN VARCHAR2 DEFAULT 'FALSE');
Table 39-4 ASSIGN_ATTRIBUTE_SET Procedure Parameters
Parameter | Description |
---|---|
|
The name of the attribute set |
|
The table storing the expression set |
|
The column in the table that stores the expressions |
|
Argument used to trust the existing expressions in a table (and skip validation) |
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" in Oracle Database Application Developer's Guide - Rules Manager and Expression Filter for more information about adding elementary attributes.
Related views: USER_EXPFIL_ATTRIBUTE_SETS
and USER_EXPFIL_EXPRESSION_SETS
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;
This procedure creates the exception table, used in validation, in the current schema.
DBMS_EXPFIL.BUILD_EXCEPTIONS_TABLE ( exception_tab IN VARCHAR2);
Table 39-5 BUILD_EXCEPTIONS_TABLE Procedure Parameter
Parameter | Description |
---|---|
|
The name of the exception table |
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" in Oracle Database Application Developer's Guide - Rules Manager and Expression Filter and VALIDATE_EXPRESSIONS Procedure in this chapter for more information.
Related view: USER_TABLES
The following command creates the exception table, InterestExceptions
, in the current schema:
BEGIN DBMS_EXPFIL.BUILD_EXCEPTIONS_TABLE ( exception_tab => 'InterestExceptions'); END;
This procedure clears the predicate statistics for the expression set stored in a table column.
DBMS_EXPFIL.CLEAR_EXPRSET_STATS ( expr_tab IN VARCHAR2, expr_col IN VARCHAR2);
Table 39-6 CLEAR_EXPRSET_STATS Procedure Parameters
Parameter | Description |
---|---|
|
The table storing the expression set |
|
The column in the table that stores the expressions |
This procedure clears the predicate statistics for the expression set stored in a table column. See also GET_EXPRSET_STATS Procedure in this chapter for information about gathering the statistics.
Related views: USER_EXPFIL_EXPRESSION_SETS
and USER_EXPFIL_EXPRSET_STATS
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;
This procedure copies an attribute set along with its user-defined function list and default index parameters to another set.
DBMS_EXPFIL.COPY_ATTRIBUTE_SET ( from_set IN VARCHAR2, to_set IN VARCHAR2);
Table 39-7 COPY_ATTRIBUTE_SET Procedure Parameters
Parameter | Description |
---|---|
|
Name of an existing attribute set to be copied |
|
Name of the new attribute set |
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
.
The following command makes a copy of the Car4Sale attribute set:
BEGIN DBMS_EXPFIL.COPY_ATTRIBUTE_SET (from_set => 'Car4Sale', to_set => 'Vehicle'); END;
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.
DBMS_EXPFIL.CREATE_ATTRIBUTE_SET ( attr_set IN VARCHAR2, from_type IN VARCHAR2 DEFAULT 'NO');
Table 39-8 CREATE_ATTRIBUTE_SET Procedure Parameters
Parameter | Description |
---|---|
|
The name of the attribute set to be created |
|
|
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 in Oracle Database Application Developer's Guide - Rules Manager and Expression Filter for more information.)
See "Defining Attribute Sets" in Oracle Database Application Developer's Guide - Rules Manager and Expression Filter and ADD_ELEMENTARY_ATTRIBUTE Procedures in this chapter for more information.
Related views: USER_EXPFIL_ATTRIBUTE_SETS
and USER_EXPFIL_ATTRIBUTES
.
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;
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.
DBMS_EXPFIL.DEFAULT_INDEX_PARAMETERS ( attr_set IN VARCHAR2, attr_list IN EXF$ATTRIBUTE_LIST, operation IN VARCHAR2 DEFAULT 'ADD');
Table 39-9 DEFAULT_INDEX_PARAMETERS Procedure Parameters
Parameter | Description |
---|---|
|
The name of the attribute set |
|
An instance of |
|
The operation to be performed on the list of index parameters. Default value: |
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 Oracle Database Application Developer's Guide - Rules Manager and Expression Filter for more information about rebuilding indexes.)
See "Creating an Index from Default Parameters" in Oracle Database Application Developer's Guide - Rules Manager and Expression Filter for more information about assigning default index parameters to an attribute set.
Related views: USER_EXPFIL_ATTRIBUTE_SETS
and USER_EXPFIL_DEF_INDEX_PARAMS
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;
This procedure adds (or drops) a partial list of XPath parameters to the default index parameters associated with the attribute set.
DBMS_EXPFIL.DEFAULT_XPINDEX_PARAMETERS ( attr_set IN VARCHAR2, xmlt_attr IN VARCHAR2, xptag_list IN EXF$XPATH_TAGS, operation IN VARCHAR2 DEFAULT 'ADD');
Table 39-10 DEFAULT_XPINDEX_PARAMETERS Procedure Parameters
Parameter | Description |
---|---|
|
The name of the attribute set |
|
The name of the attribute with the |
|
An instance of |
|
The operation to be performed on the list of index parameters. Default value: |
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"in Oracle Database Application Developer's Guide - Rules Manager and Expression Filter for more information about XPath parameters to the default index parameters of an attribute set. See also DEFAULT_INDEX_PARAMETERS Procedure in this chapter for more information about default index parameters.
Related views: USER_EXPFIL_ATTRIBUTES
and USER_EXPFIL_DEF_INDEX_PARAMS
.
Note: The values assigned to thetag_name argument of exf$xpath_tag type are case sensitive. |
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;
This procedure rebuilds the bitmap indexes online and thus reduces the fragmentation.
DBMS_EXPFIL.DEFRAG_INDEX ( idx_name IN VARCHAR2);
Table 39-11 DEFRAG_INDEX Procedure Parameter
Parameter | Description |
---|---|
|
The name of the Expression Filter index |
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" in Oracle Database Application Developer's Guide - Rules Manager and Expression Filter for more information about rebuilding indexes.
Related views: USER_EXPFIL_INDEXES
and USER_INDEXES
.
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;
This procedure drops an attribute set not being used for any expression set.
DBMS_EXPFIL.DROP_ATTRIBUTE_SET ( attr_set IN VARCHAR2);
Table 39-12 DROP_ATTRIBUTE_SET Procedure Parameter
Parameter | Description |
---|---|
|
The name of the attribute set to be dropped |
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
.
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;
This procedure computes the predicate statistics for an expression set and stores them in the expression filter dictionary.
DBMS_EXPFIL.GET_EXPRSET_STATS ( expr_tab IN VARCHAR2, expr_col IN VARCHAR2);
Table 39-13 GET_EXPRSET_STATS Procedure Parameters
Parameter | Description |
---|---|
|
The table storing the expression set |
|
The column in the table that stores the expressions |
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"in Oracle Database Application Developer's Guide - Rules Manager and Expression Filter for more information about using predicate statistics.
Related views: USER_EXPFIL_EXPRESSION_SETS
and USER_EXPFIL_EXPRSET_STATS
.
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;
This procedure grants privileges on one or more Expression columns to other users.
DBMS_EXPFIL.GRANT_PRIVILEGE ( expr_tab IN VARCHAR2, expr_col IN VARCHAR2, priv_type IN VARCHAR2, to_user IN VARCHAR2);
Table 39-14 GRANT_PRIVILEGE Procedure Parameters
Parameter | Description |
---|---|
|
The table storing the expression set |
|
The column in the table that stores the expressions |
|
Type of the privilege to be granted. Valid values: INSERT EXPRESSION, UPDATE EXPRESSION, ALL. |
|
The user to whom the privilege is to be granted |
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 in this chapter and "Granting and Revoking Privileges" in Oracle Database Application Developer's Guide - Rules Manager and Expression Filter for more information about granting and revoking privileges.
Related views: USER_EXPFIL_EXPRESSION_SETS
and USER_EXPFIL_PRIVILEGES
.
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;
This procedure fine-tunes the index parameters for each expression set before index creation.
DBMS_EXPFIL.INDEX_PARAMETERS ( expr_tab IN VARCHAR2, expr_col IN VARCHAR2, attr_list IN EXF$ATTRIBUTE_LIST, operation IN VARCHAR2 DEFAULT 'ADD');
Table 39-15 INDEX_PARAMETERS Procedure Parameters
Parameter | Description |
---|---|
|
The table storing the expression set |
|
The column in the table that stores the expressions. |
|
An instance of |
|
The operation to be performed on the list of index parameters. Default value: |
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" in Oracle Database Application Developer's Guide - Rules Manager and Expression Filter and XPINDEX_PARAMETERS Procedure in this chapter for more information.
Related views: USER_EXPFIL_EXPRESSION_SETS
, USER_EXPFIL_DEF_INDEX_PARAMS
and USER_EXPFIL_INDEX_PARAMS
.
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.
This procedure modifies the list of common operators associated with a certain attribute in the attribute set.
DBMS_EXPFIL.MODIFY_OPERATOR_LIST ( attr_set IN VARCHAR2, attr_name IN VARCHAR2, attr_oper IN EXF$INDEXOPER);
Table 39-16 MODIFY_OPERATOR_LIST Procedure Parameters
Parameter | Description |
---|---|
|
The name of the attribute set |
|
The name of the stored or indexed attribute being modified |
|
The new list of operators that are frequently used in the predicates with the attribute |
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.
Related views: USER_EXPFIL_DEF_INDEX_PARAMS
The following command modifies the operator list associated with the HorsePower(Model,Year)
attribute defined in the Car4Sale
attribute set.
BEGIN DBMS_EXPFIL.MODIFY_OPERATOR_LIST ( attr_set => 'Car4Sale', attr_name => 'HorsePower(Model, Year)', attr_oper => exf$indexoper('=','<','>', 'between')); END;
This procedure revokes an expression privilege previously granted by the owner.
DBMS_EXPFIL.REVOKE_PRIVILEGE ( expr_tab IN VARCHAR2, expr_col IN VARCHAR2, priv_type IN VARCHAR2, from_user IN VARCHAR2);
Table 39-17 REVOKE_PRIVILEGE Procedure Parameters
Parameter | Description |
---|---|
|
The table storing the expression set |
|
The column in the table that stores the expressions |
|
Type of privilege to be revoked |
|
The user from whom the privilege is to be revoked |
The REVOKE_PRIVILEGE
procedure revokes an expression privilege previously granted by the owner.
See GRANT_PRIVILEGE Procedure in this chapter and "Granting and Revoking Privileges" in Oracle Database Application Developer's Guide - Rules Manager and Expression Filter for more information about granting and revoking privileges.
Related views: USER_EXPFIL_EXPRESSION_SETS
and USER_EXPFIL_PRIVILEGES
.
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;
This procedure unassigns an attribute set from a column storing expressions.
DBMS_EXPFIL.UNASSIGN_ATTRIBUTE_SET ( expr_tab IN VARCHAR2, expr_col IN VARCHAR2);
Table 39-18 UNASSIGN_ATTRIBUTE_SET Procedure Parameters
Parameter | Description |
---|---|
|
The table storing the expression set |
|
The column in the table that stores the expressions |
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 in this chapter for information about assigning attribute sets.
Related views: USER_EXPFIL_EXPRESSION_SETS
and USER_EXPFIL_INDEXES
.
The following command unassigns the attribute set previously assigned to the Interest
column of the Consumer
table. (See "Bulk Loading of Expression Data" in Oracle Database Application Developer's Guide - Rules Manager and Expression Filter.)
BEGIN DBMS_EXPFIL.UNASSIGN_ATTRIBUTE_SET (expr_tab => 'Consumer', expr_col => 'Interest'); END;
This procedure validates all the expressions in a set.
DBMS_EXPFIL.VALIDATE_EXPRESSIONS ( expr_tab IN VARCHAR2, expr_col IN VARCHAR2, exception_tab IN VARCHAR2 DEFAULT NULL);
Table 39-19 VALIDATE_EXPRESSIONS Procedure Parameters
Parameter | Description |
---|---|
|
The table storing the expression set |
|
The column in the table that stores the expressions |
|
The name of the exception table. This table is created using the |
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" in Oracle Database Application Developer's Guide - Rules Manager and Expression Filter and BUILD_EXCEPTIONS_TABLE Procedure in this chapter for more information.
Related views: USER_EXPFIL_EXPRESSION_SETS
, USER_EXPFIL_ASET_FUNCTIONS
, and USER_EXPFIL_PREDTAB_ATTRIBUTES
.
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;
This procedure is used in conjunction with the INDEX_PARAMETERS
procedure to fine-tune the XPath-specific index parameters for each expression set.
DBMS_EXPFIL.XPINDEX_PARAMETERS ( expr_tab IN VARCHAR2, expr_col IN VARCHAR2, xmlt_attr IN VARCHAR2, xptag_list IN EXF$XPATH_TAGS, operation IN VARCHAR2 DEFAULT 'ADD');
Table 39-20 XPINDEX_PARAMETERS Procedure Parameters
Parameter | Description |
---|---|
|
The table storing the expression set |
|
The column in the table that stores the expressions |
|
The name of the attribute with the |
|
An instance of |
|
The operation to be performed on the list of index parameters. Default value: |
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 in this chapter and "Index Tuning for XPath Predicates" in Oracle Database Application Developer's Guide - Rules Manager and Expression Filter for more information.
Related views: USER_EXPFIL_ATTRIBUTES
, USER_EXPFIL_DEF_INDEX_PARAMS
, and USER_EXPFIL_INDEX_PARAMS
.
Note: The values assigned to thetag_name argument of exf$xpath_tag type are case-sensitive. |
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;