15 SQL Operators and Statements

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 Language Reference.

Table 15-1 Expression Filter Index Creation and Usage Statements

Statement	Description
EVALUATE	Matches an expression set with a given data item or table of data items
ALTER INDEX REBUILD	Rebuilds an Expression Filter index
ALTER INDEX RENAME TO	Changes the name of an Expression Filter index
CREATE INDEX	Creates an Expression Filter index on a column storing expressions
DROP INDEX	Drops an Expression Filter index

EVALUATE

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.

Format

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)

Keywords and Parameters

expression_column: Name of the column storing the expressions
attribute_name: Name of an attribute from the corresponding attribute set
attribute_value: Value for the attribute
attribute_set_instance: Instance of the object type associated with the corresponding attribute set

Returns

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.

Usage Notes

You can use the EVALUATE operator in the WHERE clause of a SQL statement. When you define an Expression Filter index 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. You can use the EVALUATE operator as a join predicate between a table storing expressions and a table storing the corresponding data items.

If you can represent the values of all elementary attributes in the attribute set as readable values, such as those stored in VARCHAR, DATE, and NUMBER data types and the constructors formatted as a string, then you can format the data item 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 you can format a list of values provided for the data item 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.

You can format any data item using an AnyData instance. An attribute set with one or more binary typed attributes must use the AnyData form of the data item.

See Section 11.3 for more information about the EVALUATE operator.

Examples

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;

ALTER INDEX REBUILD

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.

Format

ALTER INDEX [schema_name.]index_name REBUILD
 [PARAMETERS ('DEFAULT')]

Keywords and 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.

Usage Notes

When you issue the ALTER INDEX...REBUILD statement without a PARAMETERS clause, the Expression Filter index is rebuilt using the current list of stored and indexed attributes. You can also use this statement for indexes that failed during IMPORT operation due to missing dependent objects.

You can modify the default index parameters associated with an attribute set without affecting the existing Expression Filter indexes. You can rebuild these indexes to use the new set of defaults by using the DEFAULT parameter with the ALTER INDEX...REBUILD statement. When you rebuild an index using the defaults, index parameters assigned to the expression set are cleared.

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 Section 12.8 for more information about rebuilding indexes.

Examples

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');

ALTER INDEX RENAME TO

The ALTER INDEX RENAME TO statement renames an Expression Filter index.

Format

ALTER INDEX [schema_name.]index_name RENAME TO new_index_name;

Keywords and Parameters

None.

Usage Notes

None.

Examples

The following statement renames the index:

ALTER INDEX InterestIndex RENAME TO ExprIndex;

CREATE INDEX

The CREATE INDEX statement creates an Expression Filter index for a set of expressions stored in a column. You should configure the column being indexed 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).

Format

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

Keywords and Parameters

EXFSYS.EXPFILTER: The name of the index type that implements the Expression Filter index.
ADD TO DEFAULTS: When you specify this parameter, 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.
REPLACE DEFAULTS: When you specify this parameter, 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.
STOREATTRS: Lists the stored attributes for the Expression Filter index.
INDEXATTRS: Lists the indexed attributes for the Expression Filter index.
TOP: You can use this parameter 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.
PREDSTORAGE: Storage clause for the predicate table. See Oracle Database SQL Language Reference for the <storage_clause> definition.

Usage Notes

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 18 and the "USER_EXPFIL_INDEX_PARAMS View" in Chapter 19.)

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 you do not specify the REPLACE DEFAULTS clause, this list is merged with the default index parameters associated with the corresponding attribute set.

If you do not specify the REPLACE DEFAULTS clause, 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 you use the TOP clause for the STOREATTRS parameter, the INDEXATTRS parameter (if specified) should also use the TOP clause. Also, the number you specify for the TOP clause of the INDEXATTRS parameter should be less than or equal to the one you specify for the STOREATTRS parameter. When you use a TOP clause, 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. You can specify an alternate tablespace and other storage parameters for the predicate table using the PREDSTORAGE clause. The indexes on the predicate table are always created in the same tablespace as the predicate table.

See Chapter 12 for information about indexing expressions.

Examples

When you do not directly assign index parameters 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) ');

You can use the TOP clause 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');

DROP INDEX

The DROP INDEX statement drops an Expression Filter index.

Format

DROP INDEX [schema_name.]index_name;

Keyword and Parameters

None.

Usage Notes

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.

Examples

DROP INDEX InterestIndex;