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