This chapter provides reference information about the SQL EVALUATE
operator and SQL statements used to index expression data. Table 15-1 lists the statements and their descriptions. For complete information about SQL statements, see Oracle Database SQL Reference.
Table 15-1 Expression Filter Index Creation and Usage Statements
Statement | Description |
---|---|
Matches an expression set with a given data item or table of data items |
|
Rebuilds an Expression Filter index |
|
Changes the name of an Expression Filter index |
|
Creates an Expression Filter index on a column storing expressions |
|
Drops an Expression Filter index |
The EVALUATE
operator is used in the WHERE
clause of a SQL statement to compare stored expressions to incoming data items.
The expressions to be evaluated are stored in an Expression column, which is created by assigning an attribute set to a VARCHAR2
column in a user table.
EVALUATE (expression_column, <dataitem>) <dataitem> := <varchar_dataitem> | <anydata_dataitem> <varchar_dataitem> := attribute_name => attribute_value {, attribute_name => attribute_value} <anydata_dataitem> := AnyData.convertObject(attribute_set_instance)
Name of the column storing the expressions
Name of an attribute from the corresponding attribute set
Value for the attribute
Instance of the object type associated with the corresponding attribute set
The EVALUATE
operator returns a 1 for an expression that matches the data item, and returns a 0 for an expression that does not match the data item. For any null values stored in the Expression column, the EVALUATE
operator returns NULL
.
The EVALUATE
operator can be used in the WHERE
clause of a SQL statement. When an Expression Filter index is defined on a column storing expressions, the EVALUATE
operator on such a column may use the index for the expression set evaluation based on its usage cost. The EVALUATE
operator can be used as a join predicate between a table storing expressions and a table storing the corresponding data items.
If the values of all elementary attributes in the attribute set can be represented as readable values, such as those stored in VARCHAR
, DATE
, and NUMBER
data types and the constructors formatted as a string, then the data item can be formatted as a string of attribute name-value pairs. If a data item does not require a constructor for any of its elementary attribute values, then a list of values provided for the data item can be formatted as a string of name-value pairs using two getVarchar methods (a STATIC
method and a MEMBER
method) in the object type associated with the attribute set.
Any data item can be formatted using an AnyData
instance. An attribute set with one or more binary typed attributes must use the AnyData
form of the data item.
See Chapter10 for more information about the EVALUATE
operator.
Related views: USER_EXPFIL_ATTRIBUTE_SETS
, USER_EXPFIL_ATTRIBUTES
, and USER_EXPFIL_EXPRESSION_SETS
The following query uses the VARCHAR
form of the data item generated by the getVarchar()
function:
SELECT * FROM Consumer WHERE EVALUATE (Consumer.Interest, Car4Sale('Mustang', 2000, 18000, 22000).getVarchar() ) = 1;
For the previous query, the data item can be passed in the AnyData
form with the following syntax:
SELECT * FROM Consumer WHERE EVALUATE (Consumer.Interest, AnyData.convertObject ( Car4Sale ('Mustang', 2000, 18000, 22000) )) = 1;
When a large set of data items are stored in a table, the table storing expressions can be joined with the table storing data items with the following syntax:
SELECT i.CarId, c.CId, c.Phone FROM Consumer c, Inventory i WHERE EVALUATE (c.Interest, Car4Sale(i.Model, i.Year, i.Price, i.Mileage).getVarchar()) = 1 ORDER BY i.CarId;
The ALTER INDEX REBUILD
statement rebuilds an Expression Filter index created on a column storing expressions. The Expression Filter index DOMIDX_OPSTATUS
status in the USER_INDEXES
view must be VALID
for the rebuild operation to succeed.
ALTER INDEX [schema_name.]index_name REBUILD [PARAMETERS ('DEFAULT')]
The list of stored and indexed attributes for the Expression Filter index being rebuilt are derived from the default index parameters associated with the corresponding attribute set.
When the ALTER INDEX...REBUILD
statement is issued without a PARAMETERS
clause, the Expression Filter index is rebuilt using the current list of stored and indexed attributes. This statement can also be used for indexes that failed during IMPORT
operation due to missing dependent objects.
The default index parameters associated with an attribute set can be modified without affecting the existing Expression Filter indexes. These indexes can be rebuilt to use the new set of defaults by using the DEFAULT
parameter with the ALTER INDEX...REBUILD
statement. Index parameters assigned to the expression set are cleared when an index is rebuilt using the defaults.
The bitmap indexes defined for the indexed attributes of an Expression Filter index get fragmented as the expressions stored in the corresponding column are frequently modified (using INSERT
, UPDATE
, or DELETE
operations). Rebuilding those indexes could improve the performance of the query using the EVALUATE
operator. The bitmap indexes can be rebuilt online using the DBMS_EXPFIL.DEFRAG_INDEX
procedure.
See Chapter11 for more information about rebuilding indexes.
Related views: USER_EXPFIL_INDEXES
and USER_EXPFIL_PREDTAB_ATTRIBUTES
The following statement rebuilds the index using its current parameters:
ALTER INDEX InterestIndex REBUILD;
The following statement rebuilds the index using the default index parameters associated with the corresponding attribute set:
ALTER INDEX InterestIndex REBUILD PARAMETERS('DEFAULT');
The ALTER INDEX RENAME TO
statement renames an Expression Filter index.
ALTER INDEX [schema_name.]index_name RENAME TO new_index_name;
None.
None.
The following statement renames the index:
ALTER INDEX InterestIndex RENAME TO ExprIndex;
The CREATE INDEX
statement creates an Expression Filter index for a set of expressions stored in a column. The column being indexed should be configured to store expressions (with an attribute set assigned to it), and the index should be created in the same schema as the table (storing expressions).
CREATE INDEX [schema_name.]index_name ON [schema_name.].table_name (column_name) INDEXTYPE IS EXFSYS.EXPFILTER [ PARAMETERS (' <parameters_clause> ' ) ...; <parameters_clause>:= [ADD TO DEFAULTS | REPLACE DEFAULTS] [<storeattrs_clause>] [<indexattrs_clause>][<predstorage_clause>] <storeattrs_clause> := STOREATTRS [ ( attr1, attr2, ..., attrx ) | TOP n ] <indexattrs_clause> := INDEXATTRS [ ( attr1, attr2, ..., attry ) | TOP m ] <predstorage_clause> := PREDSTORAGE (<storage_clause>)
The name of the index type that implements the Expression Filter index.
When this parameter is specified, the attributes listed in the STOREATTRS
and INDEXATTRS
clauses are added to the defaults associated with the corresponding attribute set. This is the default behavior.
When this parameter is specified, the index is created using only the list of stored and indexed attributes specified after this clause. In this case, the default index parameters associated with the corresponding attribute set are ignored.
Lists the stored attributes for the Expression Filter index.
Lists the indexed attributes for the Expression Filter index.
This parameter can be used for both STOREATTRS
and INDEXATTRS
clauses only when expression set statistics are collected. (See the section about GET_EXPRSET_STATS Procedure.) The number after the TOP
parameter indicates the number of (the most-frequent) attributes to be stored or indexed for the Expression Filter index.
Storage clause for the predicate table. See Oracle Database SQL Reference for the <storage_clause>
definition.
When the index parameters are directly assigned to an expression set (column storing expressions), the PARAMETERS
clause in the CREATE INDEX
statement cannot contain STOREATTRS
or INDEXATTRS
clauses. In this case, the Expression Filter index is always created using the parameters associated with the expression set. (See the INDEX_PARAMETERS
Procedure and XPINDEX_PARAMETERS
Procedure sections in Chapter 17 and the "USER_EXPFIL_INDEX_PARAMS View" in Chapter 18.)
When the PARAMETERS
clause is not used with the CREATE INDEX
statement and the index parameters are not assigned to the expression set, the default index parameters associated with the corresponding attribute set are used for the Expression Filter index. If the default index parameters list is empty, all the scalar attributes defined in the attribute set are stored and indexed in the predicate table.
For an Expression Filter index, all the indexed attributes are also stored. So, the list of stored attributes is derived from those listed in the STOREATTRS
clause and those listed in the INDEXATTRS
clause. If REPLACE DEFAULTS
clause is not specified, this list is merged with the default index parameters associated with the corresponding attribute set.
If the REPLACE DEFAULTS
clause is not specified, the list of indexed attributes for an Expression Filter index is derived from the INDEXATTRS
clause and the default index parameters associated with the corresponding attribute set. If this list is empty, the system picks at most 10 stored attributes and indexes them.
If an attribute is listed in the PARAMETERS
clause as well as the default index parameters, its stored versus indexed property is decided by the PARAMETERS
clause specification.
Predicate statistics for the expression set should be available to use the TOP
clause in the parameters of the CREATE INDEX
statement. (See the GET_EXPRSET_STATS
Procedure for more information.) When the TOP
clause is used for the STOREATTRS
parameter, the INDEXATTRS
parameter (if specified) should also use the TOP
clause. Also, the number specified for the TOP
clause of the INDEXATTRS
parameter should be less than or equal to the one specified for the STOREATTRS
parameter. When a TOP
clause is used, REPLACE DEFAULTS
usage is implied. That is, the stored and indexed attributes are picked solely based on the predicate statistics available in the dictionary.
The successful creation of the Expression Filter index creates a predicate table, one or more bitmap indexes on the predicate table, and a package with access functions in the same schema as the base table. By default the predicate table and its indexes are created in the user default tablespace. Alternate tablespace and other storage parameters for the predicate table can be specified using the PREDSTORAGE
clause. The indexes on the predicate table are always created in the same tablespace as the predicate table.
See Chapter 11 for information about indexing expressions.
Related views: USER_EXPFIL_INDEXES
, USER_EXPFIL_INDEX_PARAMETERS
, USER_EXPFIL_DEF_INDEX_PARAMS
, USER_EXPFIL_EXPRSET_STATS
, and USER_EXPFIL_PREDTAB_ATTRIBUTES
When index parameters are not directly assigned to the expression set, you can create an Expression Filter index using the default index parameters specified for the corresponding attribute set as follows:
CREATE INDEX InterestIndex ON Consumer (Interest) INDEXTYPE IS EXFSYS.EXPFILTER;
You can create an index with one additional stored attribute using the following statement:
CREATE INDEX InterestIndex ON Consumer (Interest) INDEXTYPE IS EXFSYS.EXPFILTER PARAMETERS ('STOREATTRS (CrashTestRating(Model, Year)) PREDSTORAGE (tablespace tbs_1) ');
You can specify the complete list of stored and indexed attributes for an index with the following statement:
CREATE INDEX InterestIndex ON Consumer (Interest) INDEXTYPE IS EXFSYS.EXPFILTER PARAMETERS ('REPLACE DEFAULTS STOREATTRS (Model, CrashTestRating(Model, Year)) INDEXATTRS (Model, Year, Price) PREDSTORAGE (tablespace tbs_1) ');
The TOP
clause can be used in the parameters clause when statistics are computed for the expression set. These statistics are accessible from the USER_EXPFIL_EXPRSET_STATS
view.
BEGIN DBMS_EXPFIL.GET_EXPRSET_STATS (expr_tab => 'Consumer', expr_col => 'Interest'); END; / CREATE INDEX InterestIndex ON Consumer (Interest) INDEXTYPE IS EXFSYS.EXPFILTER PARAMETERS ('STOREATTRS TOP 4 INDEXATTRS TOP 3');
The DROP INDEX
statement drops an Expression Filter index.
DROP INDEX [schema_name.]index_name;
None.
Dropping an Expression Filter index automatically drops all the secondary objects maintained for the index. These objects include a predicate table, one or more indexes on the predicate table, and an access function package.
DROP INDEX InterestIndex;