Oracle® Database PL/SQL Packages and Types Reference 10g Release 2 (10.2) Part Number B14258-01 |
|
|
View PDF |
The Expression Filter feature is supplied with a set of predefined types and public synonyms for these types. Most of these types are used for configuring index parameters with the Expression Filter procedural APIs. The EXF$TABLE_ALIAS
type is used to support expressions defined on one or more database tables.
See Also:
Oracle Database Application Developer's Guide - Rules Manager and Expression Filter for more information.This chapter contains the following topics:
Table 186-1 describes the Expression Filter object types.
All the values and names passed to the types defined in this chapter are not case sensitive. To preserve the case, you use double quotation marks around the values.
Table 186-1 Expression Filter Object Types
Object Type Name | Description |
---|---|
EXF$ATTRIBUTE |
Specifies the stored and indexed attributes for the Expression Filter indexes |
EXF$ATTRIBUTE_LIST |
Specifies a list of stored and indexed attributes when configuring index parameters |
EXF$INDEXOPER |
Specifies a list of common operators in predicates with a stored or an indexed attribute |
EXF$TABLE_ALIAS |
Indicates a special form of elementary attribute used to manage expressions defined on one or more database tables |
EXF$XPATH_TAG |
Configures an XML element or an XML attribute for indexing a set of XPath predicates |
EXF$XPATH_TAGS |
Specifies a list of XML tags when configuring the Expression Filter index parameters |
The EXF$ATTRIBUTE
type is used to handle stored and indexed attributes for the Expression Filter indexes.
Syntax
CREATE or REPLACE TYPE EXF$ATTRIBUTE AS OBJECT attr_name VARCHAR2(350), attr_oper EXF$INDEXOPER, attr_indexed VARCHAR2(5);
Attributes
Table 186-2 EXF$ATTRIBUTE Object Type Attributes
Attribute | Description |
---|---|
attr_name |
The arithmetic expression that constitutes the stored or indexed attribute |
attr_oper |
The list of common operators in the predicates with the attribute. Default value: EXF$INDEXOPER('all') |
attr_indexed |
TRUE if the attribute is indexed, else FALSE. Default value: FALSE . |
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')
The EXF$ATTRIBUTE_LIST
type is used to specify a list of stored and indexed attributes while configuring the index parameters.
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') )
The EXF$INDEXOPER
type is used to specify the list of common operators in predicates with a stored or an indexed attribute.
Syntax
CREATE or REPLACE TYPE EXFSYS.EXF$INDEXOPER as VARRAY(20) of VARCHAR2(15);
The values for the EXF$INDEXOPER
array are expected to be from the list in the following table:
Value | Predicate Description |
---|---|
= |
Equality predicates |
> | Greater than predicates |
< |
Less than predicates |
>= | Greater than or equal to predicates |
<= |
Less than or equal to predicates |
!= or <> or ^= | Not equal to predicates |
IS NULL |
IS NULL predicates |
IS NOT NULL |
IS NOT NULL predicates |
ALL |
All the operators listed in this table starting with the equality predicate through the IS NOT NULL predicate |
NVL |
Predicates with NVL (equality) operator |
LIKE |
Predicates with LIKE operator |
BETWEEN |
BETWEEN predicates |
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')
A EXF$TABLE_ALIAS
type is a special form of elementary attribute that can be included in the attribute set. These attributes are used to manage expressions defined on one or more database tables.
Syntax
CREATE or REPLACE TYPE EXF$TABLE_ALIAS AS OBJECT table_name VARCHAR2(70);
Attributes
Table 186-3 EXF$TABLE_ALIAS Attribute
Attribute | Description |
---|---|
table_name |
Name of the table with a possible schema extension |
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
datatype. (Also see Appendix A in Oracle Database Application Developer's Guide - Rules Manager and Expression Filter 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.
The EXF$XPATH_TAG
type is used to configure an XML element or an XML attribute for indexing a set of XPath predicates.
Syntax
CREATE or REPLACE TYPE EXF$XPATH_TAG AS OBJECT tag_name VARCHAR2(350), tag_indexed VARCHAR2(5), tag_type VARCHAR2(30);
Attributes
Table 186-4 EXF$XPATH_TAG Attributes
Attribute | Description |
---|---|
tag_name |
Name of the XML element or attribute. The name for an XML attribute is formatted as: <ElementName>@<AttributeName> . Optionally, the element name can be prefixed with its namespace URL as in <Namespace URL>:<ElementName>@<AttributeName>. |
tag_indexed |
TRUE if XML tag is indexed; otherwise FALSE .
Default:
|
tag_type |
Datatype for the value in the case of value filter. NULL for positional filters. |
Usage Notes
EXF$XPATH_TAG
type is used to configure an XML element or an attribute as a positional or a value filter for an Expression Filter index (see "Index Tuning for XPath Predicates" in Oracle Database Application Developer's Guide - Rules Manager and Expression Filter). 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
The EXF$XPATH_TAGS
type is used to specify a list of XML tags while configuring the Expression Filter index parameters.
Syntax
CREATE or REPLACE TYPE EXF$XPATH_TAGS as VARRAY(490) of EXF$XPATH_TAG;
Attributes
None.
Usage Notes
EXF$XPATH_TAGS
type is used to specify 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 )