Rem Rem $Header: rdbms/admin/exfview.sql /main/11 2009/01/08 11:05:03 ayalaman Exp $ Rem Rem exfview.sql Rem Rem Copyright (c) 2002, 2008, Oracle. All rights reserved. Rem Rem NAME Rem exfview.sql - EXpression Filter VIEW definitions. Rem Rem DESCRIPTION Rem Catalog views to obtain information about expression sets, Rem their metadata and the expression filter indexes. Rem Rem NOTES Rem See Documentation. Rem Rem MODIFIED (MM/DD/YY) Rem ayalaman 07/23/08 - compiled sparse preds Rem ayalaman 10/30/07 - bug 6596055 Rem ayalaman 09/02/05 - text index errors Rem ayalaman 07/31/05 - contains operator in stored expressions Rem ayalaman 07/23/03 - attribute set with default valued attributes Rem ayalaman 03/03/03 - predicate table plans Rem ayalaman 01/20/03 - xpath filter views Rem ayalaman 11/07/02 - define sch_expfil_indexes view Rem ayalaman 10/21/02 - modify priv name Rem ayalaman 10/15/02 - all_.* views for export/import Rem ayalaman 10/09/02 - define def_index_parameters view Rem ayalaman 09/26/02 - ayalaman_expression_filter_support Rem ayalaman 09/06/02 - Created Rem REM REM Create Expression Filter catalog views REM prompt .. creating Expression Filter catalog views /*************** [USER/ALL/ADM]_EXPFIL_ATTRIBUTE_SETS *********************/ -- -- USER_EXPFIL_ATTRIBUTE_SETS -- create or replace view USER_EXPFIL_ATTRIBUTE_SETS (ATTRIBUTE_SET_NAME) as select atsname from exf$attrset where atsowner = (select user from dual); create or replace public synonym USER_EXPFIL_ATTRIBUTE_SETS for exfsys.USER_EXPFIL_ATTRIBUTE_SETS; grant select on USER_EXPFIL_ATTRIBUTE_SETS to public; COMMENT ON TABLE user_expfil_attribute_sets IS 'List of all the attribute sets in the current schema'; COMMENT ON COLUMN user_expfil_attribute_sets.attribute_set_name IS 'Name of the attribute set'; --- --- ALL_EXPFIL_ATTRIBUTE_SETS --- (use privs of the associated ADT) create or replace view ALL_EXPFIL_ATTRIBUTE_SETS (OWNER, ATTRIBUTE_SET_NAME) as select atsowner, atsname from exf$attrset ast, all_types ao where ao.owner = ast.atsowner and ao.type_name = ast.atsname; create or replace public synonym ALL_EXPFIL_ATTRIBUTE_SETS for exfsys.ALL_EXPFIL_ATTRIBUTE_SETS; grant select on ALL_EXPFIL_ATTRIBUTE_SETS to public; COMMENT ON TABLE all_expfil_attribute_sets IS 'List of all the attribute sets accessible to the user'; COMMENT ON COLUMN all_expfil_attribute_sets.owner IS 'Owner of the attribute set'; COMMENT ON COLUMN all_expfil_attribute_sets.attribute_set_name IS 'Name of the attribute set'; --- --- ADM_EXPFIL_ATTRIBUTE_SETS --- create or replace view ADM_EXPFIL_ATTRIBUTE_SETS (OWNER, ATTRIBUTE_SET_NAME) as select atsowner, atsname from exf$attrset; COMMENT ON TABLE adm_expfil_attribute_sets IS 'List of all the attribute sets in the current instance'; COMMENT ON COLUMN adm_expfil_attribute_sets.owner IS 'Owner of the attribute set'; COMMENT ON COLUMN adm_expfil_attribute_sets.attribute_set_name IS 'Name of the attribute set'; /********************* [USER/ALL]_EXPFIL_ATTRIBUTES ************************/ --- NOTE : Order by clause for this view is important for the correctness --- of the expeng implementation. Atleast ordering should be --- maintained among elementary attributes (same order as they appear --- in the corresponding type definition). This order by is used to --- generate the APIs in the type and access function that ensure the --- arguments in the correct order --- --- USER_EXPFIL_ATTRIBUTES --- create or replace view USER_EXPFIL_ATTRIBUTES (ATTRIBUTE_SET_NAME, ATTRIBUTE, DATA_TYPE, ASSOCIATED_TABLE, DEFAULT_VALUE, TEXT_PREFERENCES) as select atsname, attrname, attrtype, decode (bitand(attrprop, 16), 16, attrtptab, null), attrdefvl, decode (bitand(attrprop, 32), 32, attrtxtprf, 'N/A') from exf$attrlist where atsowner = (select user from dual) order by atsowner, atsname, elattrid; create or replace public synonym USER_EXPFIL_ATTRIBUTES for exfsys.USER_EXPFIL_ATTRIBUTES; grant select on USER_EXPFIL_ATTRIBUTES to public; COMMENT ON TABLE user_expfil_attributes IS 'List of all the elementary attributes in the current schema'; COMMENT ON COLUMN user_expfil_attributes.attribute_set_name IS 'Name of the attribute set this attribute belongs to'; COMMENT ON COLUMN user_expfil_attributes.attribute IS 'Name of the attribute'; COMMENT ON COLUMN user_expfil_attributes.data_type IS 'Datatype of the attribute'; COMMENT ON COLUMN user_expfil_attributes.associated_table IS 'Table associated with table alias attribute'; COMMENT ON COLUMN user_expfil_attributes.default_value IS 'String representation of the default value for the attribute'; COMMENT ON COLUMN user_expfil_attributes.text_preferences IS 'Preferences for an attribute configured for text predicates'; --- --- ALL_EXPFIL_ATTRIBUTES --- (use the privs of the associated ADT) create or replace view ALL_EXPFIL_ATTRIBUTES (OWNER, ATTRIBUTE_SET_NAME, ATTRIBUTE, DATA_TYPE, ASSOCIATED_TABLE, DEFAULT_VALUE, TEXT_PREFERENCES) as select atsowner, atsname, attrname, attrtype, decode (bitand(attrprop, 16), 16, attrtptab, null), attrdefvl, decode (bitand(attrprop, 32), 32, attrtxtprf, 'N/A') from exf$attrlist, all_types ao where atsowner = ao.owner and atsname = ao.type_name order by atsowner, atsname, elattrid; create or replace public synonym ALL_EXPFIL_ATTRIBUTES for exfsys.ALL_EXPFIL_ATTRIBUTES; grant select on ALL_EXPFIL_ATTRIBUTES to public; COMMENT ON TABLE all_expfil_attributes IS 'List of all the elementary attributes accessible to the user'; COMMENT ON COLUMN all_expfil_attributes.owner IS 'Owner of the attribute set'; COMMENT ON COLUMN all_expfil_attributes.attribute_set_name IS 'Name of the attribute set this attribute belongs to'; COMMENT ON COLUMN all_expfil_attributes.attribute IS 'Name of the attribute'; COMMENT ON COLUMN all_expfil_attributes.data_type IS 'Datatype of the attribute'; COMMENT ON COLUMN all_expfil_attributes.associated_table IS 'Table associated with table alias attribute'; COMMENT ON COLUMN all_expfil_attributes.default_value IS 'String representation of the default value for the attribute'; COMMENT ON COLUMN all_expfil_attributes.text_preferences IS 'Preferences for an attribute configured for text predicates'; /******************* [USER/ALL]_EXPFIL_DEF_INDEX_PARAMS *******************/ -- NOTE : This view lists all the attributes that are configired as -- stored attributes. These attributes may additionally have INDEXED -- property set (implying bitmap index on the attribute) and have -- operator list set. Note that XPath parameters(attributes) are -- also listed in this view. Additional info about XPath tags can be -- obtained from USER_EXPFIL_XPATH_TAGS view. --- --- USER_EXPFIL_DEF_INDEX_PARAMS --- create or replace view USER_EXPFIL_DEF_INDEX_PARAMS (ATTRIBUTE_SET_NAME, ATTRIBUTE, DATA_TYPE, ELEMENTARY, INDEXED, OPERATOR_LIST, XMLTYPE_ATTR) as select atsname, attrsexp, attrtype, decode (bitand(attrprop, 1), 1, 'YES','NO'), decode (bitand(attrprop, 8), 8, 'YES','NO'), varray2str(attroper), xmltattr from exf$defidxparam where atsowner = (select user from dual); create or replace public synonym USER_EXPFIL_DEF_INDEX_PARAMS for exfsys.USER_EXPFIL_DEF_INDEX_PARAMS; grant select on USER_EXPFIL_DEF_INDEX_PARAMS to public; COMMENT ON TABLE user_expfil_def_index_params IS 'List of all the stored attributes in the current schema'; COMMENT ON COLUMN user_expfil_def_index_params.attribute_set_name IS 'Name of the attribute set this attribute belongs to'; COMMENT ON COLUMN user_expfil_def_index_params.attribute IS 'Name of the attribute'; COMMENT ON COLUMN user_expfil_def_index_params.data_type IS 'Datatype of the attribute'; COMMENT ON COLUMN user_expfil_def_index_params.elementary IS 'Field to indicate if the attribute is elementary'; COMMENT ON COLUMN user_expfil_def_index_params.indexed IS 'Field to indicate if the attribute is indexed in the predicate table'; COMMENT ON COLUMN user_expfil_def_index_params.operator_list IS 'List of common operators for the attribute'; COMMENT ON COLUMN user_expfil_def_index_params.xmltype_attr IS 'The XMLType attribute for which the current XPath attribute is defined'; --- --- ALL_EXPFIL_DEF_INDEX_PARAMS --- create or replace view ALL_EXPFIL_DEF_INDEX_PARAMS (OWNER, ATTRIBUTE_SET_NAME, ATTRIBUTE, DATA_TYPE, ELEMENTARY, INDEXED, OPERATOR_LIST, XMLTYPE_ATTR) as select atsowner, atsname, attrsexp, attrtype, decode (bitand(attrprop, 1), 1, 'YES','NO'), decode (bitand(attrprop, 8), 8, 'YES','NO'), varray2str(attroper), xmltattr from exf$defidxparam, all_types ao where atsowner = ao.owner and atsname = ao.type_name; create or replace public synonym ALL_EXPFIL_DEF_INDEX_PARAMS for exfsys.ALL_EXPFIL_DEF_INDEX_PARAMS; grant select on ALL_EXPFIL_DEF_INDEX_PARAMS to public; COMMENT ON TABLE all_expfil_def_index_params IS 'List of all the stored attributes accessible to the user'; COMMENT ON COLUMN all_expfil_def_index_params.owner IS 'Owner of the attribute set'; COMMENT ON COLUMN all_expfil_def_index_params.attribute_set_name IS 'Name of the attribute set this attribute belongs to'; COMMENT ON COLUMN all_expfil_def_index_params.attribute IS 'Name of the attribute'; COMMENT ON COLUMN all_expfil_def_index_params.data_type IS 'Datatype of the attribute'; COMMENT ON COLUMN all_expfil_def_index_params.elementary IS 'Field to indicate if the attribute is elementary'; COMMENT ON COLUMN all_expfil_def_index_params.indexed IS 'Field to indicate if the attribute is indexed in the predicate table'; COMMENT ON COLUMN all_expfil_def_index_params.operator_list IS 'List of common operators for the attribute'; COMMENT ON COLUMN all_expfil_def_index_params.xmltype_attr IS 'The XMLType attribute for which the current XPath attribute is defined'; /************************** ADM_EXPFIL_ATTRIBUTES **************************/ -- This view is a union of the USER_EXPFIL_ATTRIBUTES and -- USER_EXPFIL_DEF_INDEX_PARAMS views. This is used by the java -- implementation during index maintenance -- -- create or replace view ADM_EXPFIL_ATTRIBUTES (OWNER, ATTRIBUTE_SET_NAME, ATTRIBUTE, DATA_TYPE, ELEMENTARY, COMPLEX, STORED, INDEXED, TABLE_ALIAS, OPERATOR_LIST, XMLTYPE_ATTR, ASSOCIATED_TABLE, DEFAULT_VALUE) as ( select atsowner, atsname, attrname, attrtype, 'YES', 'NO', 'NO', 'NO', decode (bitand(attrprop, 16), 16, 'YES', 'NO'), null, null, attrtptab, attrdefvl from exf$attrlist eal where attrname not in (select attrsexp from exf$defidxparam dip where eal.atsowner = dip.atsowner and eal.atsname = dip.atsname) UNION ALL select atsowner, atsname, attrsexp, attrtype, decode (bitand(attrprop, 1), 1, 'YES','NO'), decode (bitand(attrprop, 1), 1, 'NO','YES'), 'YES', decode (bitand(attrprop, 8), 8, 'YES','NO'), 'NO', varray2str(attroper), xmltattr, null, null from exf$defidxparam where bitand(attrprop, 4) = 4 ); COMMENT ON TABLE adm_expfil_attributes IS 'List of all the attributes in the current instance'; COMMENT ON COLUMN adm_expfil_attributes.owner IS 'Owner of the attribute set'; COMMENT ON COLUMN adm_expfil_attributes.attribute_set_name IS 'Name of the attribute set this attribute belongs to'; COMMENT ON COLUMN adm_expfil_attributes.attribute IS 'Name of the attribute'; COMMENT ON COLUMN adm_expfil_attributes.data_type IS 'Datatype of the attribute'; COMMENT ON COLUMN adm_expfil_attributes.elementary IS 'Field to indicate if the attribute is elementary'; COMMENT ON COLUMN adm_expfil_attributes.complex IS 'Field to indicate if the attribute is complex'; COMMENT ON COLUMN adm_expfil_attributes.stored IS 'Field to indicate if the attribute is stored in the predicate table'; COMMENT ON COLUMN adm_expfil_attributes.indexed IS 'Field to indicate if the attribute is indexed in the predicate table'; COMMENT ON COLUMN adm_expfil_attributes.table_alias IS 'Field to indicate if the elementary attribute is a table alias'; COMMENT ON COLUMN adm_expfil_attributes.operator_list IS 'List of common operators for the attribute'; COMMENT ON COLUMN adm_expfil_attributes.xmltype_attr IS 'The XMLType attribute for which the current XPath attribute is defined'; COMMENT ON COLUMN adm_expfil_attributes.associated_table IS 'Table associated with the embedded ADT / table aliases'; COMMENT ON COLUMN adm_expfil_attributes.default_value IS 'String representation of the default value for the attribute'; /***************** [USER/ALL/ADM]_EXPFIL_INDEX_PARAMS **********************/ -- Unlike the DEFAULT index params which are associated with the attr set, -- these are the parameter for an instance of expression set (for the index) -- -- USER_EXPFIL_INDEX_PARAMS -- create or replace view USER_EXPFIL_INDEX_PARAMS (EXPSET_TABLE, EXPSET_COLUMN, ATTRIBUTE, DATA_TYPE, ELEMENTARY, INDEXED, OPERATOR_LIST, XMLTYPE_ATTR) as select esettabn, esetcoln, attrsexp, attrtype, decode (bitand(attrprop, 1), 1, 'YES','NO'), decode (bitand(attrprop, 8), 8, 'YES','NO'), varray2str(attroper), xmltattr from exf$esetidxparam where esetowner = (select user from dual); create or replace public synonym USER_EXPFIL_INDEX_PARAMS for exfsys.USER_EXPFIL_INDEX_PARAMS; grant select on USER_EXPFIL_INDEX_PARAMS to public; COMMENT ON TABLE user_expfil_index_params IS 'List of all the stored attributes for index instances in the schema'; COMMENT ON COLUMN user_expfil_index_params.expset_table IS 'Name of the table storing the expressions'; COMMENT ON COLUMN user_expfil_index_params.expset_column IS 'Name of the column storing the expressions'; COMMENT ON COLUMN user_expfil_index_params.attribute IS 'Name of the attribute'; COMMENT ON COLUMN user_expfil_index_params.data_type IS 'Datatype of the attribute'; COMMENT ON COLUMN user_expfil_index_params.elementary IS 'Field to indicate if the attribute is elementary'; COMMENT ON COLUMN user_expfil_index_params.indexed IS 'Field to indicate if the attribute is indexed in the predicate table'; COMMENT ON COLUMN user_expfil_index_params.operator_list IS 'List of common operators for the attribute'; COMMENT ON COLUMN user_expfil_index_params.xmltype_attr IS 'The XMLType attribute for which the current XPath attribute is defined'; --- --- ALL_EXPFIL_INDEX_PARAMS --- (using the privs of the table storing expressions) create or replace view ALL_EXPFIL_INDEX_PARAMS (OWNER, EXPSET_TABLE, EXPSET_COLUMN, ATTRIBUTE, DATA_TYPE, ELEMENTARY, INDEXED, OPERATOR_LIST, XMLTYPE_ATTR) as select esetowner, esettabn, esetcoln, attrsexp, attrtype, decode (bitand(attrprop, 1), 1, 'YES','NO'), decode (bitand(attrprop, 8), 8, 'YES','NO'), varray2str(attroper), xmltattr from exf$esetidxparam, all_tables ao where esetowner = ao.owner and esettabn = ao.table_name; create or replace public synonym ALL_EXPFIL_INDEX_PARAMS for exfsys.ALL_EXPFIL_INDEX_PARAMS; grant select on ALL_EXPFIL_INDEX_PARAMS to public; COMMENT ON TABLE all_expfil_index_params IS 'List of all the stored attributes for index instances accessible to the user'; COMMENT ON COLUMN all_expfil_index_params.owner IS 'Owner of the Expression Set'; COMMENT ON COLUMN all_expfil_index_params.expset_table IS 'Name of the table storing the expressions'; COMMENT ON COLUMN all_expfil_index_params.expset_column IS 'Name of the column storing the expressions'; COMMENT ON COLUMN all_expfil_index_params.attribute IS 'Name of the attribute'; COMMENT ON COLUMN all_expfil_index_params.data_type IS 'Datatype of the attribute'; COMMENT ON COLUMN all_expfil_index_params.elementary IS 'Field to indicate if the attribute is elementary'; COMMENT ON COLUMN all_expfil_index_params.indexed IS 'Field to indicate if the attribute is indexed in the predicate table'; COMMENT ON COLUMN all_expfil_index_params.operator_list IS 'List of common operators for the attribute'; COMMENT ON COLUMN all_expfil_index_params.xmltype_attr IS 'The XMLType attribute for which the current XPath attribute is defined'; --- --- ADM_EXPFIL_INDEX_PARAMS --- create or replace view ADM_EXPFIL_INDEX_PARAMS (OWNER, EXPSET_TABLE, EXPSET_COLUMN, ATTRIBUTE, DATA_TYPE, ELEMENTARY, INDEXED, OPERATOR_LIST, XMLTYPE_ATTR) as select esetowner, esettabn, esetcoln, attrsexp, attrtype, decode (bitand(attrprop, 1), 1, 'YES','NO'), decode (bitand(attrprop, 8), 8, 'YES','NO'), varray2str(attroper), xmltattr from exf$esetidxparam; COMMENT ON TABLE adm_expfil_index_params IS 'List of all the stored attributes for all index instances'; COMMENT ON COLUMN adm_expfil_index_params.owner IS 'Owner of the Expression Set'; COMMENT ON COLUMN adm_expfil_index_params.expset_table IS 'Name of the table storing the expressions'; COMMENT ON COLUMN adm_expfil_index_params.expset_column IS 'Name of the column storing the expressions'; COMMENT ON COLUMN adm_expfil_index_params.attribute IS 'Name of the attribute'; COMMENT ON COLUMN adm_expfil_index_params.data_type IS 'Datatype of the attribute'; COMMENT ON COLUMN adm_expfil_index_params.elementary IS 'Field to indicate if the attribute is elementary'; COMMENT ON COLUMN adm_expfil_index_params.indexed IS 'Field to indicate if the attribute is indexed in the predicate table'; COMMENT ON COLUMN adm_expfil_index_params.operator_list IS 'List of common operators for the attribute'; COMMENT ON COLUMN adm_expfil_index_params.xmltype_attr IS 'The XMLType attribute for which the current XPath attribute is defined'; /****************** [USER/ALL/ADM]_EXPFIL_ASET_FUNCTIONS *******************/ -- -- USER_EXPFIL_ASET_FUNCTIONS -- create or replace view USER_EXPFIL_ASET_FUNCTIONS (ATTRIBUTE_SET_NAME, UDF_NAME, OBJECT_OWNER, OBJECT_NAME, OBJECT_TYPE) as select udfasname, udfname, udfobjown, udfobjnm, udftype from exf$asudflist where udfasoner = (select user from dual); create or replace public synonym USER_EXPFIL_ASET_FUNCTIONS for exfsys.USER_EXPFIL_ASET_FUNCTIONS; grant select on USER_EXPFIL_ASET_FUNCTIONS to public; COMMENT ON TABLE USER_EXPFIL_ASET_FUNCTIONS IS 'List of approved user-defined functions for the attribute sets'; COMMENT ON COLUMN user_expfil_aset_functions.attribute_set_name IS 'Name of the attribute set'; COMMENT ON COLUMN user_expfil_aset_functions.udf_name IS 'Name of the user-defined FUNCTION/PACKAGE/TYPE'; COMMENT ON COLUMN user_expfil_aset_functions.object_owner IS 'Owner of the object'; COMMENT ON COLUMN user_expfil_aset_functions.object_name IS 'Name of the object'; COMMENT ON COLUMN user_expfil_aset_functions.object_type IS 'Type of the object - FUNCTION/PACKAGE/TYPE'; --- --- ALL_EXPFIL_ASET_FUNCTIONS --- (use privs of the associated attribute set type) create or replace view ALL_EXPFIL_ASET_FUNCTIONS (OWNER, ATTRIBUTE_SET_NAME, UDF_NAME, OBJECT_OWNER, OBJECT_NAME, OBJECT_TYPE) as select udfasoner, udfasname, udfname, udfobjown, udfobjnm, udftype from exf$asudflist, all_types ao where udfasoner = ao.owner and udfasname = ao.type_name; create or replace public synonym ALL_EXPFIL_ASET_FUNCTIONS for exfsys.ALL_EXPFIL_ASET_FUNCTIONS; grant select on ALL_EXPFIL_ASET_FUNCTIONS to public; COMMENT ON TABLE ALL_EXPFIL_ASET_FUNCTIONS IS 'List of approved user-defined functions for the attribute sets accessible to the user'; COMMENT ON COLUMN all_expfil_aset_functions.owner IS 'Owner of the attribute set'; COMMENT ON COLUMN all_expfil_aset_functions.attribute_set_name IS 'Name of the attribute set'; COMMENT ON COLUMN all_expfil_aset_functions.udf_name IS 'Name of the user-defined FUNCTION/PACKAGE/TYPE'; COMMENT ON COLUMN all_expfil_aset_functions.object_owner IS 'Owner of the object'; COMMENT ON COLUMN all_expfil_aset_functions.object_name IS 'Name of the object'; COMMENT ON COLUMN all_expfil_aset_functions.object_type IS 'Type of the object - FUNCTION/PACKAGE/TYPE'; --- --- ADM_EXPFIL_ASET_FUNCTIONS --- create or replace view ADM_EXPFIL_ASET_FUNCTIONS (OWNER, ATTRIBUTE_SET_NAME, UDF_NAME, OBJECT_OWNER, OBJECT_NAME, OBJECT_TYPE) as select udfasoner, udfasname, udfname, udfobjown, udfobjnm, udftype from exf$asudflist; COMMENT ON TABLE ADM_EXPFIL_ASET_FUNCTIONS IS 'List of approved user-defined functions for the attribute sets'; COMMENT ON COLUMN adm_expfil_aset_functions.owner IS 'Owner of the attribute set'; COMMENT ON COLUMN adm_expfil_aset_functions.attribute_set_name IS 'Name of the attribute set'; COMMENT ON COLUMN adm_expfil_aset_functions.udf_name IS 'Name of the user-defined FUNCTION/PACKAGE/TYPE'; COMMENT ON COLUMN adm_expfil_aset_functions.object_owner IS 'Owner of the object'; COMMENT ON COLUMN adm_expfil_aset_functions.object_name IS 'Name of the object'; COMMENT ON COLUMN adm_expfil_aset_functions.object_type IS 'Type of the object - FUNCTION/PACKAGE/TYPE'; /******************* [USER/ALL/ADM]_EXPFIL_XPATH_TAGS *********************/ --- --- USER_EXPFIL_XPATH_TAGS --- create or replace view USER_EXPFIL_XPATH_TAGS (ATTRIBUTE_SET_NAME, XMLTYPE_ATTRIBUTE, XPATH_TAG, DATA_TYPE, TAG_TYPE, FILTER_TYPE) as select atsname, xmltattr, attrsexp, attrtype, decode(bitand(attrprop, 32), 32, 'XML ELEMENT', decode(bitand(attrprop, 64), 64, 'XML ATTRIBUTE', null)), decode(bitand(attrprop, 128), 128, 'POSITIONAL', decode(bitand(attrprop, 256), 256, 'VALUE BASED', null)) from exf$defidxparam where xmltattr is not null and atsowner = (select user from dual); -- --create or replace public synonym USER_EXPFIL_XPATH_TAGS for -- exfsys.USER_EXPFIL_XPATH_TAGS; grant select on USER_EXPFIL_XPATH_TAGS to public; COMMENT ON TABLE user_expfil_xpath_tags IS 'List of all the XPath Tags in the attribute sets'; COMMENT ON COLUMN user_expfil_xpath_tags.attribute_set_name IS 'Name of the attribute set a XPath Tag belongs'; COMMENT ON COLUMN user_expfil_xpath_tags.xmltype_attribute IS 'Name of the XMLType attribute for which this XPath Tag is defined'; COMMENT ON COLUMN user_expfil_xpath_tags.xpath_tag IS 'Name of the XPath Tag'; COMMENT ON COLUMN user_expfil_xpath_tags.data_type IS 'Datatype of the values for the XPath tag'; COMMENT ON COLUMN user_expfil_xpath_tags.tag_type IS 'Type of the Tag - XML ELEMENT or XML ATTRIBUTE'; COMMENT ON COLUMN user_expfil_xpath_tags.filter_type IS 'Type of filter for the XPath tag - POSITIONAL/ VALUE BASED'; --- --- ALL_EXPFIL_XPATH_TAGS --- (use privs of the associated attribute set type) create or replace view ALL_EXPFIL_XPATH_TAGS (OWNER, ATTRIBUTE_SET_NAME, XMLTYPE_ATTRIBUTE, XPATH_TAG, DATA_TYPE, TAG_TYPE, FILTER_TYPE) as select atsowner, atsname, xmltattr, attrsexp, attrtype, decode(bitand(attrprop, 32), 32, 'XML ELEMENT', decode(bitand(attrprop, 64), 64, 'XML ATTRIBUTE', null)), decode(bitand(attrprop, 128), 128, 'POSITIONAL', decode(bitand(attrprop, 256), 256, 'VALUE BASED', null)) from exf$defidxparam, all_types ao where atsowner = ao.owner and atsname = ao.type_name and xmltattr is not null; -- --create or replace public synonym ALL_EXPFIL_ASET_FUNCTIONS -- for exfsys.ALL_EXPFIL_ASET_FUNCTIONS; -- grant select on ALL_EXPFIL_ASET_FUNCTIONS to public; COMMENT ON TABLE all_expfil_xpath_tags IS 'List of all the XPath Tags in the attribute sets accessible to the user'; COMMENT ON COLUMN all_expfil_xpath_tags.owner IS 'Schema owning the attribute set with the XPath tags'; COMMENT ON COLUMN all_expfil_xpath_tags.attribute_set_name IS 'Name of the attribute set a XPath Tag belongs'; COMMENT ON COLUMN all_expfil_xpath_tags.xmltype_attribute IS 'Name of the XMLType attribute for which this XPath Tag is defined'; COMMENT ON COLUMN all_expfil_xpath_tags.xpath_tag IS 'Name of the XPath Tag'; COMMENT ON COLUMN all_expfil_xpath_tags.data_type IS 'Datatype of the values for the XPath tag'; COMMENT ON COLUMN all_expfil_xpath_tags.tag_type IS 'Type of the Tag - XML ELEMENT or XML ATTRIBUTE'; COMMENT ON COLUMN all_expfil_xpath_tags.filter_type IS 'Type of filter for the XPath tag - POSITIONAL/ VALUE BASED'; --- --- ADM_EXPFIL_XPATH_TAGS --- create or replace view ADM_EXPFIL_XPATH_TAGS (OWNER, ATTRIBUTE_SET_NAME, XMLTYPE_ATTRIBUTE, XPATH_TAG, DATA_TYPE, TAG_TYPE, FILTER_TYPE) as select atsowner, atsname, xmltattr, attrsexp, attrtype, decode(bitand(attrprop, 32), 32, 'XML ELEMENT', decode(bitand(attrprop, 64), 64, 'XML ATTRIBUTE', null)), decode(bitand(attrprop, 128), 128, 'POSITIONAL', decode(bitand(attrprop, 256), 256, 'VALUE BASED', null)) from exf$defidxparam where xmltattr is not null; COMMENT ON TABLE adm_expfil_xpath_tags IS 'List of all the XPath Tags in the attribute sets'; COMMENT ON COLUMN adm_expfil_xpath_tags.owner IS 'Schema owning the attribute set with the XPath tags'; COMMENT ON COLUMN adm_expfil_xpath_tags.attribute_set_name IS 'Name of the attribute set a XPath Tag belongs'; COMMENT ON COLUMN adm_expfil_xpath_tags.xmltype_attribute IS 'Name of the XMLType attribute for which this XPath Tag is defined'; COMMENT ON COLUMN adm_expfil_xpath_tags.xpath_tag IS 'Name of the XPath Tag'; COMMENT ON COLUMN adm_expfil_xpath_tags.data_type IS 'Datatype of the values for the XPath tag'; COMMENT ON COLUMN adm_expfil_xpath_tags.tag_type IS 'Type of the Tag - XML ELEMENT or XML ATTRIBUTE'; COMMENT ON COLUMN adm_expfil_xpath_tags.filter_type IS 'Type of filter for the XPath tag - POSITIONAL/ VALUE BASED'; /***************** [USER/SCH/ALL/ADM]_EXPFIL_INDEXES ***********************/ --- --- USER_EXPFIL_INDEXES --- create or replace view USER_EXPFIL_INDEXES (INDEX_NAME, PREDICATE_TABLE, ACCESS_FUNC_PACKAGE, ATTRIBUTE_SET, EXPRESSION_TABLE, EXPRESSION_COLUMN, STATUS, FUNC_CPU_COST, FUNC_IO_COST, INDEX_SELECTIVITY, INDEX_CPU_COST, INDEX_IO_COST) as select io.idxname, io.idxpredtab, io.idxaccfunc, io.idxattrset, io.idxesettab, idxesetcol, io.idxstatus, io.optfccpuct, io.optfcioct, io.optixselvt, io.optixcpuct, io.optixioct from exf$idxsecobj io where io.idxowner = (select user from dual); create or replace public synonym USER_EXPFIL_INDEXES for exfsys.USER_EXPFIL_INDEXES; grant select on USER_EXPFIL_INDEXES to public; COMMENT ON TABLE user_expfil_indexes IS 'List of all the expression filter indexes in this schema'; COMMENT ON COLUMN user_expfil_indexes.index_name IS 'Name of the index'; COMMENT ON COLUMN user_expfil_indexes.predicate_table IS 'Predicate table associated with the index'; COMMENT ON COLUMN user_expfil_indexes.access_func_package IS 'System generated package that implements the index''s access function'; COMMENT ON COLUMN user_expfil_indexes.attribute_set IS 'Name of the attribute set used for this index'; COMMENT ON COLUMN user_expfil_indexes.expression_table IS 'The table storing the expression set corresponding to this index'; COMMENT ON COLUMN user_expfil_indexes.expression_column IS 'The column storing the expression set corresponding to this index'; COMMENT ON COLUMN user_expfil_indexes.status IS 'The current status of the index : VALID, FAILED [IMP], INPROGRESS'; COMMENT ON COLUMN user_expfil_indexes.func_cpu_cost IS 'Estimated CPU cost for function based evaluation of each expression'; COMMENT ON COLUMN user_expfil_indexes.func_io_cost IS 'Estimated I/O cost for function based evaluation of each expression'; COMMENT ON COLUMN user_expfil_indexes.index_selectivity IS 'Estimated selectivity of the index'; COMMENT ON COLUMN user_expfil_indexes.index_cpu_cost IS 'Estimated CPU cost for the index based evaluation of expressions'; COMMENT ON COLUMN user_expfil_indexes.index_io_cost IS 'Estimated I/O cost for the index based evaluation of expressions'; --- --- SCH_EXPFIL_INDEXES --- This view is used by the IndexStart implementation and is not --- required by the end user. This view performs better than the --- ALL_EXPFIL_INDEXES as it does not need priviledge info. --- The user query already goes through privilege checks for the --- table and the ind$ index entry. --- create or replace view SCH_EXPFIL_INDEXES (INDEX_NAME, PREDICATE_TABLE, ACCESS_FUNC_PACKAGE, ATTRIBUTE_SET, EXPRESSION_TABLE, EXPRESSION_COLUMN, FUNC_CPU_COST, FUNC_IO_COST, INDEX_SELECTIVITY, INDEX_CPU_COST, INDEX_IO_COST, PTAB_FULLIO_COST) as select io.idxname, io.idxpredtab, io.idxaccfunc, io.idxattrset, io.idxesettab, idxesetcol, io.optfccpuct, io.optfcioct, io.optixselvt, io.optixcpuct, io.optixioct, io.optptfscct from exf$idxsecobj io where io.idxowner = SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'); grant select on exfsys.SCH_EXPFIL_INDEXES to public; --- --- ALL_EXPFIL_INDEXES --- (use privs of the index) create or replace view ALL_EXPFIL_INDEXES (OWNER, INDEX_NAME, PREDICATE_TABLE, ACCESS_FUNC_PACKAGE, ATTRIBUTE_SET, EXPRESSION_TABLE, EXPRESSION_COLUMN, STATUS, FUNC_CPU_COST, FUNC_IO_COST, INDEX_SELECTIVITY, INDEX_CPU_COST, INDEX_IO_COST) as select io.idxowner, io.idxname, io.idxpredtab, io.idxaccfunc, io.idxattrset, io.idxesettab, idxesetcol, io.idxstatus, io.optfccpuct, io.optfcioct, io.optixselvt, io.optixcpuct, io.optixioct from exf$idxsecobj io, all_indexes ai where io.idxowner = ai.owner and io.idxname = ai.index_name; create or replace public synonym ALL_EXPFIL_INDEXES for exfsys.ALL_EXPFIL_INDEXES; grant select on ALL_EXPFIL_INDEXES to public; COMMENT ON TABLE all_expfil_indexes IS 'List of all the expression filter indexes in this instance'; COMMENT ON COLUMN all_expfil_indexes.owner IS 'Owner of the index'; COMMENT ON COLUMN all_expfil_indexes.index_name IS 'Name of the index'; COMMENT ON COLUMN all_expfil_indexes.predicate_table IS 'Predicate table associated with the index'; COMMENT ON COLUMN all_expfil_indexes.access_func_package IS 'System generated package that implements the index''s access function'; COMMENT ON COLUMN all_expfil_indexes.attribute_set IS 'Name of the attribute set used for this index'; COMMENT ON COLUMN all_expfil_indexes.expression_table IS 'The table storing the expression set corresponding to this index'; COMMENT ON COLUMN all_expfil_indexes.expression_column IS 'The column storing the expression set corresponding to this index'; COMMENT ON COLUMN all_expfil_indexes.status IS 'The current status of the index : VALID, FAILED [IMP], INPROGRESS'; COMMENT ON COLUMN all_expfil_indexes.func_cpu_cost IS 'Estimated CPU cost for function based evaluation of each expression'; COMMENT ON COLUMN all_expfil_indexes.func_io_cost IS 'Estimated I/O cost for function based evaluation of each expression'; COMMENT ON COLUMN all_expfil_indexes.index_selectivity IS 'Estimated selectivity of the index'; COMMENT ON COLUMN all_expfil_indexes.index_cpu_cost IS 'Estimated CPU cost for the index based evaluation of expressions'; COMMENT ON COLUMN all_expfil_indexes.index_io_cost IS 'Estimated I/O cost for the index based evaluation of expressions'; --- --- ADM_EXPFIL_INDEXES --- create or replace view ADM_EXPFIL_INDEXES (OWNER, INDEX_NAME, PREDICATE_TABLE, ACCESS_FUNC_PACKAGE, ATTRIBUTE_SET, EXPRESSION_TABLE, EXPRESSION_COLUMN, STATUS) as select io.idxowner, io.idxname, io.idxpredtab, io.idxaccfunc, io.idxattrset, io.idxesettab, idxesetcol, io.idxstatus from exf$idxsecobj io; COMMENT ON TABLE adm_expfil_indexes IS 'List of all the expression filter indexes in this instance'; COMMENT ON COLUMN adm_expfil_indexes.owner IS 'Owner of the index'; COMMENT ON COLUMN adm_expfil_indexes.index_name IS 'Name of the index'; COMMENT ON COLUMN adm_expfil_indexes.predicate_table IS 'Predicate table associated with the index'; COMMENT ON COLUMN adm_expfil_indexes.access_func_package IS 'System generated package that implements the index''s access function'; COMMENT ON COLUMN adm_expfil_indexes.attribute_set IS 'Name of the attribute set used for this index'; COMMENT ON COLUMN adm_expfil_indexes.expression_table IS 'The table storing the expression set corresponding to this index'; COMMENT ON COLUMN adm_expfil_indexes.expression_column IS 'The column storing the expression set corresponding to this index'; COMMENT ON COLUMN adm_expfil_indexes.status IS 'The current status of the index : VALID, FAILED [IMP], INPROGRESS'; /***************** [USER/ALL/ADM]_EXPFIL_PREDTAB_ATTRIBUTES ****************/ --- --- USER_EXPFIL_PREDTAB_ATTRIBUTES --- create or replace view USER_EXPFIL_PREDTAB_ATTRIBUTES (INDEX_NAME, ATTRIBUTE_ID, ATTRIBUTE_ALIAS, SUBEXPRESSION, DATA_TYPE, STORED, INDEXED, OPERATOR_LIST, XMLTYPE_ATTR, XPTAG_TYPE, XPFILTER_TYPE) as select io.idxname, pc.ptattrid, pc.ptattralias, pc.ptattrsexp, pc.ptattrtype, decode (bitand(pc.ptattrprop, 1), 1, 'YES','NO'), decode (bitand(pc.ptattrprop, 2), 2, 'YES','NO'), varray2str(ptattroper), pc.xmltattr, decode(bitand(ptattrprop, 8), 8, 'XML ELEMENT', decode(bitand(ptattrprop, 16), 16, 'XML ATTRIBUTE', null)), decode(bitand(ptattrprop, 32), 32, 'POSITIONAL', decode(bitand(ptattrprop, 128), 128, 'CHAR VALUE', decode(bitand(ptattrprop, 256), 256, 'INT VALUE', decode(bitand(ptattrprop, 512), 512, 'DATE VALUE', null)))) from exf$predattrmap pc, exf$idxsecobj io where io.idxobj# = pc.ptidxobj# and io.idxowner = (select user from dual) and bitand(ptattrprop, 1024) = 0; create or replace public synonym USER_EXPFIL_PREDTAB_ATTRIBUTES for exfsys.USER_EXPFIL_PREDTAB_ATTRIBUTES; grant select on USER_EXPFIL_PREDTAB_ATTRIBUTES to public; COMMENT ON TABLE user_expfil_predtab_attributes IS 'List of all the predicate table attributes in this schema'; COMMENT ON COLUMN user_expfil_predtab_attributes.index_name IS 'Name of the index associated with the predicate table'; COMMENT ON COLUMN user_expfil_predtab_attributes.attribute_id IS 'Identifier for the predicate table attribute'; COMMENT ON COLUMN user_expfil_predtab_attributes.attribute_alias IS 'Alias for the predicate table attribute'; COMMENT ON COLUMN user_expfil_predtab_attributes.subexpression IS 'Sub-expression representing the complex or elementary attribute'; COMMENT ON COLUMN user_expfil_predtab_attributes.data_type IS 'Resulting datatype of the sub-expression'; COMMENT ON COLUMN user_expfil_predtab_attributes.stored IS 'Field to indicate if the attribute is stored in the predicate table'; COMMENT ON COLUMN user_expfil_predtab_attributes.indexed IS 'Field to indicate if the attribute is indexed in the predicate table'; COMMENT ON COLUMN user_expfil_predtab_attributes.operator_list IS 'List of common operators for the attribute'; COMMENT ON COLUMN user_expfil_predtab_attributes.xmltype_attr IS 'The XMLType attribute for which the current XPath attribute is defined'; COMMENT ON COLUMN user_expfil_predtab_attributes.xptag_type IS 'Type of the Tag - XML ELEMENT or XML ATTRIBUTE'; COMMENT ON COLUMN user_expfil_predtab_attributes.xpfilter_type IS 'Type of filter for the XPath tag - POSITIONAL/ [CHAR|INT|DATE] VALUE '; --- --- ALL_EXPFIL_PREDTAB_ATTRIBUTES --- (use privs of the index object) create or replace view ALL_EXPFIL_PREDTAB_ATTRIBUTES (OWNER, INDEX_NAME, ATTRIBUTE_ID, ATTRIBUTE_ALIAS, SUBEXPRESSION, DATA_TYPE, STORED, INDEXED, OPERATOR_LIST, XMLTYPE_ATTR, XPTAG_TYPE, XPFILTER_TYPE) as select io.idxowner, io.idxName, pc.ptattrid, pc.ptattralias, pc.ptattrsexp, pc.ptattrtype, decode (bitand(pc.ptattrprop, 1), 1, 'YES','NO'), decode (bitand(pc.ptattrprop, 2), 2, 'YES','NO'), varray2str(ptattroper), pc.xmltattr, decode(bitand(ptattrprop, 8), 8, 'XML ELEMENT', decode(bitand(ptattrprop, 16), 16, 'XML ATTRIBUTE', null)), decode(bitand(ptattrprop, 32), 32, 'POSITIONAL', decode(bitand(ptattrprop, 128), 128, 'CHAR VALUE', decode(bitand(ptattrprop, 256), 256, 'INT VALUE', decode(bitand(ptattrprop, 512), 512, 'DATE VALUE', null)))) from exf$predattrmap pc, exf$idxsecobj io, all_indexes ai where io.idxobj# = pc.ptidxobj# and io.idxowner = ai.owner and io.idxname = ai.index_name; create or replace public synonym ALL_EXPFIL_PREDTAB_ATTRIBUTES for exfsys.ALL_EXPFIL_PREDTAB_ATTRIBUTES; grant select on ALL_EXPFIL_PREDTAB_ATTRIBUTES to public; COMMENT ON TABLE all_expfil_predtab_attributes IS 'List of all the predicate table attributes'; COMMENT ON COLUMN all_expfil_predtab_attributes.owner IS 'Owner of the index'; COMMENT ON COLUMN all_expfil_predtab_attributes.index_name IS 'Name of the index associated with the predicate table'; COMMENT ON COLUMN all_expfil_predtab_attributes.attribute_id IS 'Identifier for the predicate table attribute'; COMMENT ON COLUMN all_expfil_predtab_attributes.attribute_alias IS 'Alias for the predicate table attribute'; COMMENT ON COLUMN all_expfil_predtab_attributes.subexpression IS 'Sub-expression representing the complex or elementary attribute'; COMMENT ON COLUMN all_expfil_predtab_attributes.data_type IS 'Resulting datatype of the sub-expression'; COMMENT ON COLUMN all_expfil_predtab_attributes.stored IS 'Field to indicate if the attribute is stored in the predicate table'; COMMENT ON COLUMN all_expfil_predtab_attributes.indexed IS 'Field to indicate if the attribute is indexed in the predicate table'; COMMENT ON COLUMN all_expfil_predtab_attributes.operator_list IS 'List of common operators for the attribute'; COMMENT ON COLUMN all_expfil_predtab_attributes.xmltype_attr IS 'The XMLType attribute for which the current XPath attribute is defined'; COMMENT ON COLUMN all_expfil_predtab_attributes.xptag_type IS 'Type of the Tag - XML ELEMENT or XML ATTRIBUTE'; COMMENT ON COLUMN all_expfil_predtab_attributes.xpfilter_type IS 'Type of filter for the XPath tag - POSITIONAL/ [CHAR|INT|DATE] VALUE '; --- --- ADM_EXPFIL_PREDTAB_ATTRIBUTES --- create or replace view ADM_EXPFIL_PREDTAB_ATTRIBUTES (OWNER, INDEX_NAME, ATTRIBUTE_ID, ATTRIBUTE_ALIAS, SUBEXPRESSION, DATA_TYPE, STORED, INDEXED, OPERATOR_LIST, XMLTYPE_ATTR, XPTAG_TYPE, XPFILTER_TYPE) as select io.idxowner, io.idxName, pc.ptattrid, pc.ptattralias, pc.ptattrsexp, pc.ptattrtype, decode (bitand(pc.ptattrprop, 1), 1, 'YES','NO'), decode (bitand(pc.ptattrprop, 2), 2, 'YES','NO'), varray2str(ptattroper), pc.xmltattr, decode(bitand(ptattrprop, 8), 8, 'XML ELEMENT', decode(bitand(ptattrprop, 16), 16, 'XML ATTRIBUTE', null)), decode(bitand(ptattrprop, 32), 32, 'POSITIONAL', decode(bitand(ptattrprop, 128), 128, 'CHAR VALUE', decode(bitand(ptattrprop, 256), 256, 'INT VALUE', decode(bitand(ptattrprop, 512), 512, 'DATE VALUE', null)))) from exf$predattrmap pc, exf$idxsecobj io where io.idxobj# = pc.ptidxobj#; COMMENT ON TABLE adm_expfil_predtab_attributes IS 'List of all the predicate table attributes'; COMMENT ON COLUMN adm_expfil_predtab_attributes.owner IS 'Owner of the index'; COMMENT ON COLUMN adm_expfil_predtab_attributes.index_name IS 'Name of the index associated with the predicate table'; COMMENT ON COLUMN adm_expfil_predtab_attributes.attribute_id IS 'Identifier for the predicate table attribute'; COMMENT ON COLUMN adm_expfil_predtab_attributes.attribute_alias IS 'Alias for the predicate table attribute'; COMMENT ON COLUMN adm_expfil_predtab_attributes.subexpression IS 'Sub-expression representing the complex or elementary attribute'; COMMENT ON COLUMN adm_expfil_predtab_attributes.data_type IS 'Resulting datatype of the sub-expression'; COMMENT ON COLUMN adm_expfil_predtab_attributes.stored IS 'Field to indicate if the attribute is stored in the predicate table'; COMMENT ON COLUMN adm_expfil_predtab_attributes.indexed IS 'Field to indicate if the attribute is indexed in the predicate table'; COMMENT ON COLUMN adm_expfil_predtab_attributes.operator_list IS 'List of common operators for the attribute'; COMMENT ON COLUMN adm_expfil_predtab_attributes.xmltype_attr IS 'The XMLType attribute for which the current XPath attribute is defined'; COMMENT ON COLUMN adm_expfil_predtab_attributes.xptag_type IS 'Type of the Tag - XML ELEMENT or XML ATTRIBUTE'; COMMENT ON COLUMN adm_expfil_predtab_attributes.xpfilter_type IS 'Type of filter for the XPath tag - POSITIONAL/ [CHAR|INT|DATE] VALUE '; /***************** [USER/ALL/ADM]_EXPFIL_EXPRESSION_SETS *******************/ --- --- USER_EXPFIL_EXPRESSION_SETS --- create or replace view USER_EXPFIL_EXPRESSION_SETS (EXPR_TABLE, EXPR_COLUMN, ATTRIBUTE_SET, LAST_ANALYZED, NUM_EXPRESSIONS, PREDS_PER_EXPR, NUM_SPARSE_PREDS) as select exstabnm, exscolnm, exsatsnm, exsetlanl, exsetnexp, avgprpexp, exsetsprp from exf$exprset where exsowner = (select user from dual); create or replace public synonym USER_EXPFIL_EXPRESSION_SETS for exfsys.USER_EXPFIL_EXPRESSION_SETS; grant select on USER_EXPFIL_EXPRESSION_SETS to public; COMMENT ON TABLE user_expfil_expression_sets IS 'List of expression sets in the current schema'; COMMENT ON COLUMN user_expfil_expression_sets.expr_table IS 'The table storing the expression set in the current schema'; COMMENT ON COLUMN user_expfil_expression_sets.expr_column IS 'The column storing the expression set'; COMMENT ON COLUMN user_expfil_expression_sets.attribute_set IS 'Attribute set used for the expression set'; COMMENT ON COLUMN user_expfil_expression_sets.last_analyzed IS 'The date of the most recent time the expression set is analyzed'; COMMENT ON COLUMN user_expfil_expression_sets.num_expressions IS 'Number of expressions (disjunctions) in the expression set'; COMMENT ON COLUMN user_expfil_expression_sets.preds_per_expr IS 'Average number of conjunctive predicates per expressions'; COMMENT ON COLUMN user_expfil_expression_sets.num_sparse_preds IS 'Number of sparse predicates in the expression set'; --- --- ALL_EXPFIL_EXPRESSION_SETS --- (use privs of the table storing expressions) create or replace view ALL_EXPFIL_EXPRESSION_SETS (OWNER, EXPR_TABLE, EXPR_COLUMN, ATTRIBUTE_SET, LAST_ANALYZED, NUM_EXPRESSIONS, PREDS_PER_EXPR, NUM_SPARSE_PREDS) as select exsowner, exstabnm, exscolnm, exsatsnm, exsetlanl, exsetnexp, avgprpexp, exsetsprp from exf$exprset, all_tables at where at.owner = exsowner and at.table_name = exstabnm; create or replace public synonym ALL_EXPFIL_EXPRESSION_SETS for exfsys.ALL_EXPFIL_EXPRESSION_SETS; grant select on ALL_EXPFIL_EXPRESSION_SETS to public; COMMENT ON TABLE all_expfil_expression_sets IS 'List of expression sets accessible to the current user'; COMMENT ON COLUMN all_expfil_expression_sets.owner IS 'Owner of the expression set'; COMMENT ON COLUMN all_expfil_expression_sets.expr_table IS 'The table storing the expression set in the owner''s schema'; COMMENT ON COLUMN all_expfil_expression_sets.expr_column IS 'The column storing the expression set'; COMMENT ON COLUMN all_expfil_expression_sets.attribute_set IS 'Attribute set used for the expression set'; COMMENT ON COLUMN all_expfil_expression_sets.last_analyzed IS 'The date of the most recent time the expression set is analyzed'; COMMENT ON COLUMN all_expfil_expression_sets.num_expressions IS 'Number of expressions (disjunctions) in the expression set'; COMMENT ON COLUMN all_expfil_expression_sets.preds_per_expr IS 'Average number of conjunctive predicates per expressions'; COMMENT ON COLUMN all_expfil_expression_sets.num_sparse_preds IS 'Number of sparse predicates in the expression set'; --- --- ADM_EXPFIL_EXPRESSION_SETS --- create or replace view ADM_EXPFIL_EXPRESSION_SETS (OWNER, EXPR_TABLE, EXPR_COLUMN, ATTRIBUTE_SET, PRIVILEGE_TRIGGER, LAST_ANALYZED, NUM_EXPRESSIONS, PREDS_PER_EXPR, NUM_SPARSE_PREDS) as select exsowner, exstabnm, exscolnm, exsatsnm, exsprvtrig, exsetlanl, exsetnexp, avgprpexp, exsetsprp from exf$exprset; COMMENT ON TABLE adm_expfil_expression_sets IS 'List of expression sets'; COMMENT ON COLUMN adm_expfil_expression_sets.owner IS 'Owner of the expression set'; COMMENT ON COLUMN adm_expfil_expression_sets.expr_table IS 'The table storing the expression set in the owner''s schema'; COMMENT ON COLUMN adm_expfil_expression_sets.expr_column IS 'The column storing the expression set'; COMMENT ON COLUMN adm_expfil_expression_sets.attribute_set IS 'Attribute set used for the expression set'; COMMENT ON COLUMN adm_expfil_expression_sets.privilege_trigger IS 'Trigger used to enforce the privileges for the expression set'; COMMENT ON COLUMN adm_expfil_expression_sets.last_analyzed IS 'The date of the most recent time the expression set is analyzed'; COMMENT ON COLUMN adm_expfil_expression_sets.num_expressions IS 'Number of expressions (disjunctions) in the expression set'; COMMENT ON COLUMN adm_expfil_expression_sets.preds_per_expr IS 'Average number of conjunctive predicates per expressions'; COMMENT ON COLUMN adm_expfil_expression_sets.num_sparse_preds IS 'Number of sparse predicates in the expression set'; /************************ [USER/ADM]_EXPFIL_PRIVELEGES *********************/ --- --- USER_EXPFIL_PRIVILEGES --- create or replace view USER_EXPFIL_PRIVILEGES (EXPSET_OWNER, EXPSET_TABLE, EXPSET_COLUMN, GRANTEE, INSERT_PRIV, UPDATE_PRIV) as select esowner, esexptab, esexpcol, esgrantee, escrtpriv, esupdpriv from exf$expsetprivs where esgrantee = 'PUBLIC' or esgrantee = (select user from dual) or esowner = (select user from dual); create or replace public synonym USER_EXPFIL_PRIVILEGES for exfsys.USER_EXPFIL_PRIVILEGES; grant select on USER_EXPFIL_PRIVILEGES to public; COMMENT ON TABLE user_expfil_privileges IS 'Privileges for Expression set modifications'; COMMENT ON COLUMN user_expfil_privileges.expset_owner IS 'Owner of the table storing the expression set. Also the grantor'; COMMENT ON COLUMN user_expfil_privileges.expset_table IS 'The table storing the expression set in the owner''s schema'; COMMENT ON COLUMN user_expfil_privileges.expset_column IS 'The column storing the expression set'; COMMENT ON COLUMN user_expfil_privileges.grantee IS 'Grantee of the privilege. PUBLIC or the current user'; COMMENT ON COLUMN user_expfil_privileges.insert_priv IS 'Current user''s privilege to create new expressions in the set'; COMMENT ON COLUMN user_expfil_privileges.update_priv IS 'Current user''s privilege to modify existing expressions in the set'; --- --- ADM_EXPFIL_PRIVELEGES --- create or replace view ADM_EXPFIL_PRIVILEGES (EXPSET_OWNER, EXPSET_TABLE, EXPSET_COLUMN, GRANTEE, INSERT_PRIV, UPDATE_PRIV) as select esowner, esexptab, esexpcol, esgrantee, escrtpriv, esupdpriv from exf$expsetprivs; COMMENT ON TABLE adm_expfil_privileges IS 'Privileges for Expression set modifications'; COMMENT ON COLUMN adm_expfil_privileges.expset_owner IS 'Owner of the table storing the expression set. Also the grantor'; COMMENT ON COLUMN adm_expfil_privileges.expset_table IS 'The table storing the expression set in the owner''s schema'; COMMENT ON COLUMN adm_expfil_privileges.expset_column IS 'The column storing the expression set'; COMMENT ON COLUMN adm_expfil_privileges.grantee IS 'Grantee of the privilege. PUBLIC implies any user'; COMMENT ON COLUMN user_expfil_privileges.insert_priv IS 'Grantee''s privilege to create new expressions in the set'; COMMENT ON COLUMN user_expfil_privileges.update_priv IS 'Grantee''s privilege to modify existing expressions in the set'; /******************* [USER/ALL/ADM]_EXPFIL_EXPRSET_STATS *******************/ --- --- USER_EXPFIL_EXPRSET_STATS --- create or replace view USER_EXPFIL_EXPRSET_STATS (EXPR_TABLE, EXPR_COLUMN, ATTRIBUTE_EXP, PCT_OCCURRENCE, PCT_EQ_OPER, PCT_LT_OPER, PCT_GT_OPER, PCT_LTEQ_OPER, PCT_GTEQ_OPER, PCT_NEQ_OPER, PCT_NUL_OPER, PCT_NNUL_OPER, PCT_BETW_OPER, PCT_NVL_OPER, PCT_LIKE_OPER) as select e.ESETTABLE, e.ESETCOLUMN, e.PREDLHS, (((NOEQPREDS+NOLTPREDS+NOGTPREDS +NOLTEQPRS+NOGTEQPRS+NONEQPRS+NOISNLPRS+NOISNNLPRS+NOBETPREDS+NONVLPREDS +NOLIKEPRS)*100)/EXSETNEXP), NOEQPREDS*100/EXSETNEXP, NOLTPREDS*100/EXSETNEXP, NOGTPREDS*100/EXSETNEXP, NOLTEQPRS*100/EXSETNEXP, NOGTEQPRS*100/EXSETNEXP, NONEQPRS*100/EXSETNEXP, NOISNLPRS*100/EXSETNEXP, NOISNNLPRS*100/EXSETNEXP, NOBETPREDS* 100/EXSETNEXP, NONVLPREDS*100/EXSETNEXP, NOLIKEPRS*100/EXSETNEXP from exf$expsetstats e, exf$exprset es where e.esetowner = es.exsowner and e.esettable = es.exstabnm and e.esetcolumn = es.exscolnm and e.esetowner = (select user from dual); create or replace public synonym USER_EXPFIL_EXPRSET_STATS for exfsys.USER_EXPFIL_EXPRSET_STATS; grant select on USER_EXPFIL_EXPRSET_STATS to public; COMMENT ON TABLE user_expfil_exprset_stats IS 'Predicate statistics for the expression sets in the current schema'; COMMENT ON COLUMN user_expfil_exprset_stats.expr_table IS 'The table storing the expression set in the current schema'; COMMENT ON COLUMN user_expfil_exprset_stats.expr_column IS 'The column storing the expression set'; COMMENT ON COLUMN user_expfil_exprset_stats.attribute_exp IS 'Sub-expression representing the complex or elementary attribute. Also the left-hand-side of predicates'; COMMENT ON COLUMN user_expfil_exprset_stats.pct_occurrence IS 'Percentage occurrence of the attribute in the expression set'; COMMENT ON COLUMN user_expfil_exprset_stats.pct_eq_oper IS 'Percentage of predicates (of the attribute) with ''='' operator'; COMMENT ON COLUMN user_expfil_exprset_stats.pct_lt_oper IS 'Percentage of predicates (of the attribute) with ''<'' operator'; COMMENT ON COLUMN user_expfil_exprset_stats.pct_gt_oper IS 'Percentage of predicates (of the attribute) with ''>'' operator'; COMMENT ON COLUMN user_expfil_exprset_stats.pct_lteq_oper IS 'Percentage of predicates (of the attribute) with ''<='' operator'; COMMENT ON COLUMN user_expfil_exprset_stats.pct_gteq_oper IS 'Percentage of predicates (of the attribute) with ''>='' operator'; COMMENT ON COLUMN user_expfil_exprset_stats.pct_neq_oper IS 'Percentage of predicates (of the attribute) with ''!='' operator'; COMMENT ON COLUMN user_expfil_exprset_stats.pct_nul_oper IS 'Percentage of predicates (of the attribute) with ''IS NULL'' operator'; COMMENT ON COLUMN user_expfil_exprset_stats.pct_nnul_oper IS 'Percentage of predicates (of the attribute) with ''IS NOT NULL'' operator'; COMMENT ON COLUMN user_expfil_exprset_stats.pct_betw_oper IS 'Percentage of predicates (of the attribute) with ''BETWEEN'' operator'; COMMENT ON COLUMN user_expfil_exprset_stats.pct_nvl_oper IS 'Percentage of predicates (of the attribute) with ''NVL'' operator'; COMMENT ON COLUMN user_expfil_exprset_stats.pct_like_oper IS 'Percentage of predicates (of the attribute) with ''LIKE'' operator'; --- --- ALL_EXPFIL_EXPRSET_STATS --- (using the privs of the expression set table) create or replace view ALL_EXPFIL_EXPRSET_STATS (OWNER, EXPR_TABLE, EXPR_COLUMN, ATTRIBUTE_EXP, PCT_OCCURRENCE, PCT_EQ_OPER, PCT_LT_OPER, PCT_GT_OPER, PCT_LTEQ_OPER, PCT_GTEQ_OPER, PCT_NEQ_OPER, PCT_NUL_OPER, PCT_NNUL_OPER, PCT_BETW_OPER, PCT_NVL_OPER, PCT_LIKE_OPER) as select e.ESETOWNER, e.ESETTABLE, e.ESETCOLUMN, e.PREDLHS, (((NOEQPREDS+NOLTPREDS+NOGTPREDS+NOLTEQPRS+NOGTEQPRS+NONEQPRS+NOISNLPRS+ NOISNNLPRS+NOBETPREDS+NONVLPREDS+NOLIKEPRS)*100)/EXSETNEXP), NOEQPREDS*100/EXSETNEXP, NOLTPREDS*100/EXSETNEXP, NOGTPREDS*100/EXSETNEXP, NOLTEQPRS*100/EXSETNEXP, NOGTEQPRS*100/EXSETNEXP, NONEQPRS*100/EXSETNEXP, NOISNLPRS*100/EXSETNEXP, NOISNNLPRS*100/EXSETNEXP, NOBETPREDS* 100/EXSETNEXP, NONVLPREDS*100/EXSETNEXP, NOLIKEPRS*100/EXSETNEXP from exf$expsetstats e, exf$exprset es, all_tables ao where e.esetowner = ao.owner and e.esettable = ao.table_name and e.esetowner = es.exsowner and e.esettable = es.exstabnm and e.esetcolumn = es.exscolnm ; create or replace public synonym ALL_EXPFIL_EXPRSET_STATS for exfsys.ALL_EXPFIL_EXPRSET_STATS; grant select on ALL_EXPFIL_EXPRSET_STATS to public; COMMENT ON TABLE all_expfil_exprset_stats IS 'Predicate statistics for the expression sets in the current schema'; COMMENT ON COLUMN all_expfil_exprset_stats.owner IS 'Owner of the table storing expressions'; COMMENT ON COLUMN all_expfil_exprset_stats.expr_table IS 'The table storing the expression set'; COMMENT ON COLUMN all_expfil_exprset_stats.expr_column IS 'The column storing the expression set'; COMMENT ON COLUMN all_expfil_exprset_stats.attribute_exp IS 'Sub-expression representing the complex or elementary attribute. Also the left-hand-side of predicates'; COMMENT ON COLUMN all_expfil_exprset_stats.pct_occurrence IS 'Percentage occurrence of the attribute in the expression set'; COMMENT ON COLUMN all_expfil_exprset_stats.pct_eq_oper IS 'Percentage of predicates (of the attribute) with ''='' operator'; COMMENT ON COLUMN all_expfil_exprset_stats.pct_lt_oper IS 'Percentage of predicates (of the attribute) with ''<'' operator'; COMMENT ON COLUMN all_expfil_exprset_stats.pct_gt_oper IS 'Percentage of predicates (of the attribute) with ''>'' operator'; COMMENT ON COLUMN all_expfil_exprset_stats.pct_lteq_oper IS 'Percentage of predicates (of the attribute) with ''<='' operator'; COMMENT ON COLUMN all_expfil_exprset_stats.pct_gteq_oper IS 'Percentage of predicates (of the attribute) with ''>='' operator'; COMMENT ON COLUMN all_expfil_exprset_stats.pct_neq_oper IS 'Percentage of predicates (of the attribute) with ''!='' operator'; COMMENT ON COLUMN all_expfil_exprset_stats.pct_nul_oper IS 'Percentage of predicates (of the attribute) with ''IS NULL'' operator'; COMMENT ON COLUMN all_expfil_exprset_stats.pct_nnul_oper IS 'Percentage of predicates (of the attribute) with ''IS NOT NULL'' operator'; COMMENT ON COLUMN all_expfil_exprset_stats.pct_betw_oper IS 'Percentage of predicates (of the attribute) with ''BETWEEN'' operator'; COMMENT ON COLUMN all_expfil_exprset_stats.pct_nvl_oper IS 'Percentage of predicates (of the attribute) with ''NVL'' operator'; COMMENT ON COLUMN all_expfil_exprset_stats.pct_like_oper IS 'Percentage of predicates (of the attribute) with ''LIKE'' operator'; --- --- ADM_EXPFIL_EXPRSET_STATS --- create or replace view ADM_EXPFIL_EXPRSET_STATS (OWNER, EXPR_TABLE, EXPR_COLUMN, ATTRIBUTE_EXP, PCT_OCCURRENCE, PCT_EQ_OPER, PCT_LT_OPER, PCT_GT_OPER, PCT_LTEQ_OPER, PCT_GTEQ_OPER, PCT_NEQ_OPER, PCT_NUL_OPER, PCT_NNUL_OPER, PCT_BETW_OPER, PCT_NVL_OPER, PCT_LIKE_OPER) as select e.ESETOWNER, e.ESETTABLE, e.ESETCOLUMN, e.PREDLHS, (((NOEQPREDS+NOLTPREDS+NOGTPREDS+NOLTEQPRS+NOGTEQPRS+NONEQPRS+NOISNLPRS+ NOISNNLPRS+NOBETPREDS+NONVLPREDS+NOLIKEPRS)*100)/EXSETNEXP), NOEQPREDS*100/EXSETNEXP, NOLTPREDS*100/EXSETNEXP, NOGTPREDS*100/EXSETNEXP, NOLTEQPRS*100/EXSETNEXP, NOGTEQPRS*100/EXSETNEXP, NONEQPRS*100/EXSETNEXP, NOISNLPRS*100/EXSETNEXP, NOISNNLPRS*100/EXSETNEXP, NOBETPREDS* 100/EXSETNEXP, NONVLPREDS*100/EXSETNEXP, NOLIKEPRS*100/EXSETNEXP from exf$expsetstats e, exf$exprset es where e.esetowner = es.exsowner and e.esettable = es.exstabnm and e.esetcolumn = es.exscolnm ; COMMENT ON TABLE adm_expfil_exprset_stats IS 'Predicate statistics for the expression sets in the current schema'; COMMENT ON COLUMN adm_expfil_exprset_stats.owner IS 'Owner of the table storing expressions'; COMMENT ON COLUMN adm_expfil_exprset_stats.expr_table IS 'The table storing the expression set'; COMMENT ON COLUMN adm_expfil_exprset_stats.expr_column IS 'The column storing the expression set'; COMMENT ON COLUMN adm_expfil_exprset_stats.attribute_exp IS 'Sub-expression representing the complex or elementary attribute. Also the left-hand-side of predicates'; COMMENT ON COLUMN adm_expfil_exprset_stats.pct_occurrence IS 'Percentage occurrence of the attribute in the expression set'; COMMENT ON COLUMN adm_expfil_exprset_stats.pct_eq_oper IS 'Percentage of predicates (of the attribute) with ''='' operator'; COMMENT ON COLUMN adm_expfil_exprset_stats.pct_lt_oper IS 'Percentage of predicates (of the attribute) with ''<'' operator'; COMMENT ON COLUMN adm_expfil_exprset_stats.pct_gt_oper IS 'Percentage of predicates (of the attribute) with ''>'' operator'; COMMENT ON COLUMN adm_expfil_exprset_stats.pct_lteq_oper IS 'Percentage of predicates (of the attribute) with ''<='' operator'; COMMENT ON COLUMN adm_expfil_exprset_stats.pct_gteq_oper IS 'Percentage of predicates (of the attribute) with ''>='' operator'; COMMENT ON COLUMN adm_expfil_exprset_stats.pct_neq_oper IS 'Percentage of predicates (of the attribute) with ''!='' operator'; COMMENT ON COLUMN adm_expfil_exprset_stats.pct_nul_oper IS 'Percentage of predicates (of the attribute) with ''IS NULL'' operator'; COMMENT ON COLUMN adm_expfil_exprset_stats.pct_nnul_oper IS 'Percentage of predicates (of the attribute) with ''IS NOT NULL'' operator'; COMMENT ON COLUMN adm_expfil_exprset_stats.pct_betw_oper IS 'Percentage of predicates (of the attribute) with ''BETWEEN'' operator'; COMMENT ON COLUMN adm_expfil_exprset_stats.pct_nvl_oper IS 'Percentage of predicates (of the attribute) with ''NVL'' operator'; COMMENT ON COLUMN adm_expfil_exprset_stats.pct_like_oper IS 'Percentage of predicates (of the attribute) with ''LIKE'' operator'; -- -- USER_EXPFIL_PREDTAB_PLAN -- (undocumented) -- This is similat to plan_table except for the index_name. The user -- is expected to use connect by clause while querying from this view. create or replace view USER_EXPFIL_PREDTAB_PLAN as select i.idxname INDEX_NAME, p.* from exf$plan_table p, exf$idxsecobj i where p.statement_id = i.idxobj# and i.idxowner = (select user from dual); grant select on user_expfil_predtab_plan to public; create or replace view ALL_EXPFIL_PREDTAB_PLAN as select i.idxowner OWNER, i.idxname INDEX_NAME, p.* from exf$plan_table p, exf$idxsecobj i where p.statement_id = i.idxobj#; grant select on all_expfil_predtab_plan to public; /********************* USER_EXPFIL_TEXT_INDEX_ERRORS ********************/ -- -- This view maps any errors with the text indexes to the expression -- column values in which the error exists -- -- The text component may not be installed. create this view -- conditionally -- declare txtviewexts NUMBER; begin begin execute immediate 'drop public synonym USER_EXPFIL_TEXT_INDEX_ERRORS'; execute immediate 'drop view exfsys.USER_EXPFIL_TEXT_INDEX_ERRORS'; exception when others then null; end; select count(*) into txtviewexts from dba_views where owner = 'CTXSYS' and view_name = 'CTX_USER_INDEX_ERRORS'; if (txtviewexts != 0) then execute immediate 'create or replace view USER_EXPFIL_TEXT_INDEX_ERRORS (expression_table, expression_column, err_timestamp, err_exprkey, err_text) as select uei.expression_table, uei.expression_column, cie.err_timestamp, exf$text2exprid(ui.table_name, cie.err_textkey), cie.err_text from user_indexes ui, user_expfil_indexes uei, ctxsys.ctx_user_index_errors cie where ui.index_name = cie.err_index_name and uei.predicate_table = ui.table_name'; execute immediate 'COMMENT ON TABLE user_expfil_text_index_errors IS ''Errors for the text predicates stored in the expressions columns'''; execute immediate 'COMMENT ON COLUMN user_expfil_text_index_errors.expression_table IS ''Table with the expression column'''; execute immediate 'COMMENT ON COLUMN user_expfil_text_index_errors.expression_column IS ''Name of the column storing expressions'''; execute immediate 'COMMENT ON COLUMN user_expfil_text_index_errors.err_timestamp IS ''Time at which the error was noticed'''; execute immediate 'COMMENT ON COLUMN user_expfil_text_index_errors.err_exprkey IS ''Key to the expression with the text predicate'''; execute immediate 'COMMENT ON COLUMN user_expfil_text_index_errors.err_text IS ''Description of the text predicate error'''; execute immediate 'grant select on user_expfil_text_index_errors to public'; execute immediate 'create or replace public synonym USER_EXPFIL_TEXT_INDEX_ERRORS for exfsys.USER_EXPFIL_TEXT_INDEX_ERRORS'; end if; exception when others then if (SQLCODE = -942) then null; else raise; end if; end; /