19 Expression Filter Views

Expression Filter defines views containing metadata using the xxx_EXPFIL prefix, where xxx can be the string USER or ALL. Expression Filter procedures create and maintain these read-only views.

Table 19-1 lists the names of the views and their descriptions.

Table 19-1 Expression Filter Views

View Name Description

USER_EXPFIL_ASET_FUNCTIONS View

List of functions and packages approved for the attribute set

USER_EXPFIL_ATTRIBUTES View

List of elementary attributes of the attribute set

USER_EXPFIL_ATTRIBUTE_SETS View

List of attribute set

USER_EXPFIL_DEF_INDEX_PARAMS View

List of default index parameters

USER_EXPFIL_EXPRESSION_SETS View

List of expression sets

USER_EXPFIL_EXPRSET_STATS View

List of predicate statistics for the expression sets

USER_EXPFIL_INDEX_PARAMS View

List of index parameters assigned to the expression set

USER_EXPFIL_INDEXES View

List of expression filter indexes

USER_EXPFIL_PREDTAB_ATTRIBUTES View

List of stored and indexed attributes for the indexes

USER_EXPFIL_PRIVILEGES View

List of all the expression privileges of the current user

USER_EXPFIL_TEXT_INDEX_ERRORS

Maps any errors with the text indexes to the expression column values in which the error exists


19.1 USER_EXPFIL_ASET_FUNCTIONS View

The USER_EXPFIL_ASET_FUNCTIONS view lists all the functions and packages that are allowed in the expressions using a particular attribute set. The following table describes the columns in this view:

Column Name Data Type Description
ATTRIBUTE_SET_NAME VARCHAR2 Name of the attribute set
UDF_NAME VARCHAR2 Name of the user-defined function or package (or type) as specified by the user (with or without the schema extension)
OBJECT_OWNER VARCHAR2 Owner of the function or package (or type)
OBJECT_NAME VARCHAR2 Name of the function or package (or type)
OBJECT_TYPE VARCHAR2 Type of the object at the time the object was added to the attribute set:
  • Function: If the object is a function

  • Package: If the object is a package

  • Type: If the object is a type

  • Embedded type: If the object is a type that is implicitly added to the function list as the type is used by one of the elementary attributes in the set

  • Synonym: Synonym to a function or package or type


19.2 USER_EXPFIL_ATTRIBUTES View

The USER_EXPFIL_ATTRIBUTES view lists all the elementary attributes of the attribute sets defined in the user's schema. The following table describes the columns in this view:

Column Name Data Type Description
ATTRIBUTE_SET_NAME VARCHAR2 Name of the attribute se
ATTRIBUTE VARCHAR2 Name of the elementary attribut.
DATA_TYPE VARCHAR2 Data type of the attribute
ASSOCIATED_TABLE VARCHAR2 Name of the corresponding table for the table alias attribute. NULL for all other types of attributes
TEXT_PREFERENCES VARCHAR2 Preferences for an attribute configured for text predicates

19.3 USER_EXPFIL_ATTRIBUTE_SETS View

The USER_EXPFIL_ATTRIBUTE_SETS view lists the attribute sets defined in the user's schema. The following table describes the columns in this view:

Column Name Data Type Description
ATTRIBUTE_SET_NAME VARCHAR2 Name of the attribute set

19.4 USER_EXPFIL_DEF_INDEX_PARAMS View

The USER_EXPFIL_DEF_INDEX_PARAMS view lists the default index parameters (stored and indexed attributes) associated with the attribute sets defined in the user's schema. The following table describes the columns in this view:

Column Name Data Type Description
ATTRIBUTE_SET_NAME VARCHAR2 Name of the attribute set
ATTRIBUTE VARCHAR2 Name of the stored attribute
DATA_TYPE VARCHAR2 Data type of the attribute
ELEMENTARY VARCHAR2 YES, if the attribute is also the elementary attribute of the attribute set; otherwise, NO
INDEXED VARCHAR2 YES, if the stored attribute is also the indexed attribute; otherwise, NO
OPERATOR_LIST VARCHAR2 String representation of the common operators configured for the attribute
XMLTYPE_ATTR VARCHAR2 Name of the corresponding XMLType elementary attribute when the stored or indexed attribute is an XML tag

19.5 USER_EXPFIL_EXPRESSION_SETS View

The USER_EXPFIL_EXPRESSION_SETS view lists the expression sets defined in the user's schema. The following table describes the columns in this view:

Column Name Data Type Description
EXPR_TABLE VARCHAR2 Name of the table storing expressions
EXPR_COLUMN VARCHAR2 Name of the column (in the table) storing expressions
ATTRIBUTE_SET VARCHAR2 Name of the corresponding attribute set
LAST_ANALYZED DATE Date on which the predicate statistics for this expression set were recently computed. NULL if statistics were not collected
NUM_EXPRESSIONS NUMBER Number of expressions in the set when the set was last analyzed
PREDS_PER_EXPR NUMBER Average number of predicates for each expression (when last analyzed)
NUM_SPARSE_PREDS NUMBER Number of sparse predicates in the expression set (when last analyzed)

19.6 USER_EXPFIL_EXPRSET_STATS View

The USER_EXPFIL_EXPRSET_STATS view lists the predicate statistics for the expression sets in the user's schema. The following table describes the columns in this view:

Column Name Data Type Description
EXPR_TABLE VARCHAR2 Name of the table storing expressions
EXPR_COLUMN VARCHAR2 Name of the column (in the table) storing expressions
ATTRIBUTE_EXP VARCHAR2 The arithmetic expression that represents a common left-hand side (LHS) in the predicates of the expression set
PCT_OCCURRENCE NUMBER Percentage occurrence of the attribute in the expression set
PCT_EQ_OPER NUMBER Percentage of predicates (of the attribute) with equality (=) operator
PCT_LT_OPER NUMBER Percentage of predicates (of the attribute) with the less than (<) operator
PCT_GT_OPER NUMBER Percentage of predicates (of the attribute) with the greater than (>) operator
PCT_LTEQ_OPER NUMBER Percentage of predicates (of the attribute) with the less than or equal to (<=) operator
PCT_GTEQ_OPER NUMBER Percentage of predicates (of the attribute) with the greater than or equal to (>=) operator
PCT_NEQ_OPER NUMBER Percentage of predicates (of the attribute) with the not equal to (!=) operator
PCT_NUL_OPER NUMBER Percentage of predicates (of the attribute) with the IS NULL operator
PCT_NNUL_OPER NUMBER Percentage of predicates (of the attribute) with the IS NOT NULL operator
PCT_BETW_OPER NUMBER Percentage of predicates (of the attribute) with the BETWEEN operator
PCT_NVL_OPER NUMBER Percentage of predicates (of the attribute) with the NVL operator
PCT_LIKE_OPER NUMBER Percentage of predicates (of the attribute) with the LIKE operator

19.7 USER_EXPFIL_INDEX_PARAMS View

The USER_EXPFIL_INDEX_PARAMS view lists the index parameters associated with the expression sets defined in the user's schema. The following table describes the columns in this view:

Column Name Data Type Description
EXPSET_TABLE VARCHAR2 Name of the table storing the expressions
EXPSET_COLUMN VARCHAR2 Name of the column storing the expressions
ATTRIBUTE VARCHAR2 Name of the stored attribute
DATA_TYPE VARCHAR2 Data type of the attribute
ELEMENTARY VARCHAR2 YES if the attribute is also the elementary attribute of the attribute set; otherwise, NO
INDEXED VARCHAR2 YES if the stored attribute is also the indexed attribute; otherwise, NO
OPERATOR_LIST VARCHAR2 String representation of the common operators configured for the attribute
XMLTYPE_ATTR VARCHAR2 Name of the corresponding XMLType elementary attribute when the stored or indexed attribute is an XML tag

19.8 USER_EXPFIL_INDEXES View

The USER_EXPFIL_INDEXES view lists the Expression Filter indexes defined in the user's schema. The following table describes the columns in this view:

Column Name Data Type Description
INDEX_NAME VARCHAR2 Name of the index
PREDICATE_TABLE VARCHAR2 Name of the predicate table used for the index
ACCESS_FUNC_PACKAGE VARCHAR2 Name of the package that defines the functions with queries on the predicate table
ATTRIBUTE_SET VARCHAR2 Name of the corresponding attribute set
EXPRESSION_TABLE VARCHAR2 Name of the table on which the index is defined
EXPRESSION_COLUMN VARCHAR2 Name of the column on which the index is defined
STATUS VARCHAR2 Index status:
  • VALID: Index was created successfully

  • FAILED: Index build failed, and it should be dropped and re-created

  • FAILED RBLD: Index build or rebuild failed, and it can be rebuilt using the ALTER INDEX REBUILD statement


19.9 USER_EXPFIL_PREDTAB_ATTRIBUTES View

The USER_EXPFIL_PREDTAB_ATTRIBUTES view shows the exact list of stored and indexed attributes used for expression filter indexes in the user's schema. The following table describes the columns in this view:

Column Name Data Type Description
INDEX_NAME VARCHAR2 Name of the index
ATTRIBUTE_ID NUMBER Attribute identifier (unique for an index)
ATTRIBUTE_ALIAS VARCHAR2 Alias given to the stored attribute
SUBEXPRESSION VARCHAR2 The arithmetic expression that represents the stored attribute (also the LHS of predicates in the set)
DATA_TYPE VARCHAR2 Derived data type for the stored attribute
INDEXED VARCHAR2 YES, if the stored attribute is also the indexed attribute; otherwise, NO
OPERATOR_LIST VARCHAR2 String representation of the common operators configured for the attribute
XMLTYPE_ATTR VARCHAR2 Name of the corresponding XMLType elementary attribute when the stored or indexed attribute is an XML tag
XPTAG_TYPE VARCHAR2 Type of the XML tag:

XML ELEMENT or XML ATTRIBUTE

XPFILTER_TYPE VARCHAR2 Type of filter configured for the XML tag: POSITIONAL or [CHAR|INT|DATE] VALUE

19.10 USER_EXPFIL_PRIVILEGES View

The USER_EXPFIL_PRIVILEGES view lists the privileges of the current user on expression sets belonging to other schemas and the privileges of other users on the expression sets owned by the current user. The following table describes the columns in this view:

Column Name Data Type Description
EXPSET_OWNER VARCHAR2 Owner of the expression set
EXPSET_TABLE VARCHAR2 Name of the table storing expressions
EXPSET_COLUMN VARCHAR2 Name of the column storing the expressions
GRANTEE VARCHAR2 Grantee of the privilege
INSERT_PRIV VARCHAR2 Y if the grantee has the INSERT EXPRESSION privilege on the expression set; otherwise, N
UPDATE_PRIV VARCHAR2 Y if the grantee has the UPDATE EXPRESSION privilege on the expression set; otherwise, N

19.11 USER_EXPFIL_TEXT_INDEX_ERRORS

The USER_EXPFIL_TEXT_INDEX_ERRORS view maps any errors with the text indexes to the expression column values in which the error exists. The following table describes the columns in this view:

Column Name Data Type Description
EXPRESSION_TABLE VARCHAR2 Table with the expression column
EXPRESSION_COLUMN VARCHAR2 Name of the column storing expressions
ERR_TIMESTAMP VARCHAR2 Time at which the error was noticed
ERR_EXPRKEY VARCHAR2 Key to the expression with the text predicate
ERR_TEXT VARCHAR2 Description of the text predicate error