248 Expression Filter Types

Syntax

CREATE or REPLACE TYPE EXF$ATTRIBUTE AS OBJECT attr_name    VARCHAR2(350),
                                               attr_oper    EXF$INDEXOPER,
                                               attr_indexed VARCHAR2(5);

Attributes

Usage Notes

• The EXF$ATTRIBUTE type is used to specify the stored and indexed attributes for an Expression Filter index using the DBMS_EXPFIL.DEFAULT_INDEX_PARAMETERS procedure. When values for attr_oper and attr_indexed fields are omitted during EXF$ATTRIBUTE instantiation, it is considered a stored attribute with a default value for common operators (EXF$INDEXOPER('all')).

Examples

A stored attribute with no preference on the list of common operators is represented as follows:

exf$attribute (attr_name => 'HorsePower(Model, Year)')

An indexed attribute is represented as follows:

exf$attribute (attr_name => 'HorsePower(Model, Year)', 
               attr_indexed => 'TRUE')

An indexed attribute with a list of common operators is represented as follows:

exf$attribute (attr_name => 'HorsePower(Model, Year)', 
               attr_oper => exf$indexoper('=','<','>','>=','<='), 
               attr_indexed => 'TRUE')

Syntax

CREATE or REPLACE TYPE EXF$ATTRIBUTE_LIST as VARRAY(490) of exf$attribute;

Attributes

None.

Usage Notes

• Also see the DEFAULT_INDEX_PARAMETERS Procedure for more information

Examples

A list of stored and indexed attributes can be represented as follows:

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

Syntax

CREATE or REPLACE TYPE EXFSYS.EXF$INDEXOPER as VARRAY(20) of VARCHAR2(15);

Attributes

None.

Usage Notes

• A value of ALL for one of the EXF$INDEXOPER items implies that all the simple operators (=,>,<,>=,<=,!=, IS NULL, IS NOT NULL) are common in the predicates with an attribute. This value can be used along with one or more complex operators (NVL, LIKE and BETWEEN).

• A predicate with a BETWEEN operator is treated as two predicates with binary operators, one with '>=' operator and another with '<=' operator. By default, only one of these operators is indexed, and the other operator is evaluated by value substitution. However, if predicates with the BETWEEN operator are common for an attribute (stored or indexed), both the binary operators resulting from the BETWEEN operator can be indexed by specifying BETWEEN in the EXF$INDEXOPER VARRAY. However, because this uses additional space in the predicate table, this operator should be used only when majority of predicates with an attribute use the BETWEEN operator.

• When the LIKE operator is chosen as one of the common operators for an attribute, LIKE predicates on that attributes are indexed. Indexing a LIKE operator is beneficial only if the VARCHAR2 constant on the right-hand side of the predicate does not lead with a wild-card character. For example, indexing a LIKE operator will filter the following predicates efficiently:

  company LIKE 'General%'
  company LIKE 'Proctor%'
  

  But, the following predicates are evaluated as sparse predicates in the last stage:

  company LIKE '%Electric'
  company LIKE "%Gamble'
  

Examples

An attribute with a list of common operators is represented as follows:

exf$attribute (attr_name => 'HorsePower(Model, Year)', 
               attr_oper => exf$indexoper('=','<','>','>=','<=', 'between'), 
               attr_indexed => 'TRUE')

Syntax

CREATE or REPLACE TYPE EXF$TABLE_ALIAS AS OBJECT table_name VARCHAR2(70);

Attributes

Usage Notes

• The concept of a table alias attribute is captured in the Expression Filter dictionary and the corresponding attribute in the attribute set's object type is created with a VARCHAR2 data type. (Also see Oracle Database Rules Manager and Expression Filter Developer's Guide and ADD_ELEMENTARY_ATTRIBUTE Procedures.)

Examples

For a set of expressions defined on database tables, the corresponding table alias attributes are configured as follows:

BEGIN
  DBMS_EXPFIL.ADD_ELEMENTARY_ATTRIBUTE (
                               attr_set  => 'HRAttrSet',
                               attr_name => 'EMP',
                               tab_alias => exf$table_alias('SCOTT.EMP'));
  DBMS_EXPFIL.ADD_ELEMENTARY_ATTRIBUTE (
                               attr_set  => 'HRAttrSet',
                               attr_name => 'DEPT',
                               tab_alias => exf$table_alias('DEPT'));
END;
/

The Expression column using the previous attribute set can store expressions of form EMP.JOB = 'Clerk' and EMP.NAME = 'Joe', where JOB and NAME are the names of the columns in the SCOTT.EMP table.

Syntax

CREATE or REPLACE TYPE EXFSYS.EXF$TEXT AS OBJECT(preferences VARCHAR2(1000));

Attributes

Usage Notes

• The EXF$TEXT attribute is used to specify the preferences for a text attribute at the time of creation. The preferences specified through the instance of EXF$TEXT type are used in creation of the CTXRULE index for the text predicates. The syntax for the text preference specification is similar to the PARAMETERS clause specified for a CTXRULE Indextype. [See CREATE INDEX syntax for CTXRULE Indextype in Oracle Text Reference, Release 10g Release 2.]

Examples

A text predicate with a LEXER and WORDLIST preferences can be created using the following instance of EXF$TEXT object as follows:

EXF$TEXT ('LEXER insrpt_lexer WORDLIST insrpt_wordlist');

Syntax

CREATE or REPLACE TYPE EXF$XPATH_TAG AS OBJECT tag_name    VARCHAR2(350),
                                               tag_indexed VARCHAR2(5),
                                               tag_type    VARCHAR2(30);

Attributes

Usage Notes

• EXF$XPATH_TAG type configures an XML element or an attribute as a positional or a value filter for an Expression Filter index (see the section on index tuning for XPath predicates in Oracle Database Rules Manager and Expression Filter Developer's Guide). An instance of the EXF$XPATH_TAG type with NULL value for tag_type configures the XML tag as a positional filter. In the current release, the only other possible values for the tag_type attribute are strings (CHAR or VARCHAR) and such tags are configured as value filters. By default, all positional filters are indexed and the value filters are not indexed. This behavior can be overridden by setting a TRUE or FALSE value for the tag_indexed attribute accordingly.

Examples

An XML element can be configured as a positional filter and be indexed using the following instance of the EXF$XPATH_TAG type.

exf$xpath_tag(tag_name    => 'stereo',       --- XML element
              tag_indexed => 'TRUE',         --- indexed predicate group
              tag_type    => null)           --- positional filter

An XML attribute can be configured as a value filter and be indexed using the following type instance.

exf$xpath_tag(tag_name    => 'stereo@make',  --- XML attribute
              tag_indexed => 'TRUE',         --- indexed predicate group
              tag_type    => 'VARCHAR(15)')  --- value filter

The following commands configure the two filters shown previously using the namespace URL for the corresponding elements.

exf$xpath_tag(tag_name => 'http://www.auto.com/car.xsd:stereo', 
              tag_indexed => 'TRUE', --- indexed predicate group
              tag_type => null)      --- positional filter

exf$xpath_tag(tag_name => 'http://www.auto.com/car.xsd:stereo@make'
              tag_indexed => 'TRUE',     --- indexed predicate group
              tag_type => 'VARCHAR(15)') --- value filter

Syntax

CREATE or REPLACE TYPE EXF$XPATH_TAGS as VARRAY(490) of EXF$XPATH_TAG;

Attributes

None.

Usage Notes

• EXF$XPATH_TAGS type specifies a list of XML tags while configuring the Expression Filter index parameters. (See DEFAULT_INDEX_PARAMETERS Procedure.)

Examples

A list of XML tags configured as positional and value filters can be represented as follows:

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
          )