8 Management Procedures Using the DBMS_EXPFIL Package</tit le> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <meta http-equiv="Content-Language" content="en" /> <meta h ttp-equiv="Content-Style-Type" content="text/css" /> <meta http-equiv="Content-Script-Type" content="text/javascript" /> <meta name=" robots" content="all" scheme="http://www.robotstxt.org/" /> <meta name="doctitle" content="Oracle® Database Application Developer 's Guide - Expression Filter 10g Release 1 (10.1)" /> <meta name="partno" content="B10821-01" /> <link rel="Start" href="../../index. htm" title="Home" type="text/html" /> <link rel="Copyright" href="../../dcommon/html/cpyr.htm" title="Copyright" type="text/html" /> <link rel="Stylesheet" href="../../dcommon/css/doccd.css" title="Default" type="text/css" /> <link rel="Contents" href="toc.htm" titl e="Contents" type="text/html" /> <link rel="Index" href="index.htm" title="Index" type="text/html" /> <link rel="Prev" href="types.ht m" title="Previous" type="text/html" /> <link rel="Next" href="views.htm" title="Next" type="text/html" /> <link rel="alternate" href ="../b10821.pdf" title="PDF version" type="application/pdf" /> </head> <body> <div class="header"> <p><a name="top" id="top" href="#B EGIN">Skip Headers</a></p> <table summary="" cellspacing="0" cellpadding="0" width="100%"> <tr> <td align="left" valign="top"><font c olor="#330099" face="Helvetica, Arial, sans-serif"><strong>Oracle® Database Application Developer's Guide - Expression Filter<br /> 10<i>g</i> Release 1 (10.1)</strong><br /> Part Number B10821-01</font></td> <td valign="bottom" align="right"> <table summary="" cellspacing="0" cellpadding="0" width="270"> <tr> <td align="center" valign="top"><a href="../../index.htm"><img src="../../dcommon/ gifs/prodicon.gif" alt="Go to Documentation Home" border="0" /><br /> <font size="-2">Home</font></a></td> <td align="center" valign= "top"><a href="../../nav/portal_3.htm"><img src="../../dcommon/gifs/bookicon.gif" alt="Go to Book List" border="0" /><br /> <font siz e="-2">Book List</font></a></td> <td align="center" valign="top"><a href="toc.htm"><img src="../../dcommon/gifs/conticon.gif" alt="Go to Table of Contents" border="0" /><br /> <font size="-2">Contents</font></a></td> <td align="center" valign="top"><a href="index.ht m"><img src="../../dcommon/gifs/indxicon.gif" alt="Go to Index" border="0" /><br /> <font size="-2">Index</font></a></td> <td align=" center" valign="top"><a href="../../mix$101/b12039/toc.htm"><img src="../../dcommon/gifs/mix.gif" alt="Go to Master Index" border="0" /><br /> <font size="-2">Master Index</font></a></td> <td align="center" valign="top"><a href="../../dcommon/html/feedback.htm" ><img src="../../dcommon/gifs/feedback.gif" alt="Go to Feedback page" border="0" /><br /> <font size="-2">Feedback</font></a></td></t r></table></td></tr></table> <hr /> <table summary="layout table" cellspacing="0" cellpadding="0" width="100%"> <tr> <td align="left" valign="top"> <table summary="layout table" cellspacing="0" cellpadding="0" align="left" width="90"> <tr> <td align="center" valign= "top"><a href="types.htm"><img src="../../dcommon/gifs/larrow.gif" alt="Go to previous page" border="0" /><br /> <font size="-2">Prev ious</font></a></td> <td align="center" valign="top"><a href="views.htm"><img src="../../dcommon/gifs/rarrow.gif" alt="Go to next pag e" border="0" /><br /> <font size="-2">Next</font></a></td></tr></table></td> <td align="right" valign="top"><a href="../b10821.pdf"> <font size="-2">View PDF</font></a></td></tr></table> <a name="BEGIN" id="BEGIN"></a></div> <div class="IND"><a id ="CIHEJDDH" name="CIHEJDDH"></a><a id="sthref270" name="sthref270"></a> <h1><font face="arial, helvetica, sans-serif" color="#330099 "><span class="secnum">8</span> Management Procedures Using the DBMS_EXPFIL Package</font></h1> <p>The Expression Filter DBMS_EXPFIL package contains all the procedures used to manage attribute sets, expression sets, expression indexes, optimizer statistics, and pri vileges. <a href="#CJAIIEHA">Table 8-1</a> describes the procedures in the DBMS_EXPFIL package. These procedures are further describe d in this chapter.</p> <p>All the values and names passed to the procedures defined in the DBMS_EXPFIL package are not case sensitive , unless otherwise mentioned. To preserve the case, you use double quotation marks around the values.</p> <div class="table"><a id="s thref271" name="sthref271"></a><a id="CJAIIEHA" name="CJAIIEHA"></a> <p><strong><em><font face="arial, helvetica, sans-serif">Table 8-1 DBMS_EXPFIL Procedures</font></em></strong></p> <table class="table-border" title="DBMS_EXPFIL Procedures" summary="This two colu mn table (read from right to left) lists the name of the procedures in the DBMS_EXPFIL package and gives a description of the procedu re." dir="ltr" border="1" width="100%" frame="hsides" rules="groups" cellpadding="3" cellspacing="0"> <thead> <tr align="left" valign ="top"> <th align="left" valign="bottom" id="r1c1-t2"><font face="Arial, Helvetica, sans-serif"><strong>Procedure</strong></font></th > <th align="left" valign="bottom" id="r1c2-t2"><font face="Arial, Helvetica, sans-serif"><strong>Description</strong></font></th></t r></thead> <tbody> <tr align="left" valign="top"> <td align="left" id="r2c1-t2" headers="r1c1-t2">ADD_ELEMENTARY_ATTRIBUTE</td> <td a lign="left" headers="r2c1-t2 r1c2-t2">Adds the specified attribute to the attribute set.</td></tr> <tr align="left" valign="top"> <td align="left" id="r3c1-t2" headers="r1c1-t2">ADD_FUNCTIONS</td> <td align="left" headers="r3c1-t2 r1c2-t2">Adds a function, type, or package to the approved list of functions with an attribute set.</td></tr> <tr align="left" valign="top"> <td align="left" id="r4c1-t 2" headers="r1c1-t2">ASSIGN_ATTRIBUTE_SET</td> <td align="left" headers="r4c1-t2 r1c2-t2">Assigns an attribute set to a column storin g expressions.</td></tr> <tr align="left" valign="top"> <td align="left" id="r5c1-t2" headers="r1c1-t2">BUILD_EXCEPTIONS_TABLE</td> < td align="left" headers="r5c1-t2 r1c2-t2">Creates an exception table to hold references to invalid expressions.</td></tr> <tr align=" left" valign="top"> <td align="left" id="r6c1-t2" headers="r1c1-t2">CLEAR_EXPRSET_STATS</td> <td align="left" headers="r6c1-t2 r1c2-t 2">Clears the predicate statistics for an expression set.</td></tr> <tr align="left" valign="top"> <td align="left" id="r7c1-t2" head ers="r1c1-t2">COPY_ATTRIBUTE_SET</td> <td align="left" headers="r7c1-t2 r1c2-t2">Makes a copy of the attribute set.</td></tr> <tr ali gn="left" valign="top"> <td align="left" id="r8c1-t2" headers="r1c1-t2">CREATE_ATTRIBUTE_SET</td> <td align="left" headers="r8c1-t2 r 1c2-t2">Creates an attribute set.</td></tr> <tr align="left" valign="top"> <td align="left" id="r9c1-t2" headers="r1c1-t2">DEFAULT_IN DEX_PARAMETERS</td> <td align="left" headers="r9c1-t2 r1c2-t2">Assigns default index parameters to an attribute set.</td></tr> <tr al ign="left" valign="top"> <td align="left" id="r10c1-t2" headers="r1c1-t2">DEFAULT_XPINDEX_PARAMETERS</td> <td align="left" headers="r 10c1-t2 r1c2-t2">Assigns default XPath index parameters to an attribute set.</td></tr> <tr align="left" valign="top"> <td align="left " id="r11c1-t2" headers="r1c1-t2">DEFRAG_INDEX</td> <td align="left" headers="r11c1-t2 r1c2-t2">Rebuilds the bitmap indexes online to reduce fragmentation.</td></tr> <tr align="left" valign="top"> <td align="left" id="r12c1-t2" headers="r1c1-t2">DROP_ATTRIBUTE_SET</ td> <td align="left" headers="r12c1-t2 r1c2-t2">Drops an unused attribute set.</td></tr> <tr align="left" valign="top"> <td align="le ft" id="r13c1-t2" headers="r1c1-t2">GET_EXPRSET_STATS</td> <td align="left" headers="r13c1-t2 r1c2-t2">Collects predicate statistics for an expression set.</td></tr> <tr align="left" valign="top"> <td align="left" id="r14c1-t2" headers="r1c1-t2">GRANT_PRIVILEGE</td> <td align="left" headers="r14c1-t2 r1c2-t2">Grants an expression DML privilege to a user.</td></tr> <tr align="left" valign="top"> < td align="left" id="r15c1-t2" headers="r1c1-t2">INDEX_PARAMETERS</td> <td align="left" headers="r15c1-t2 r1c2-t2">Assigns index param eters to an expression set.</td></tr> <tr align="left" valign="top"> <td align="left" id="r16c1-t2" headers="r1c1-t2">REVOKE_PRIVILEG E</td> <td align="left" headers="r16c1-t2 r1c2-t2">Revokes an expression DML privilege from a user.</td></tr> <tr align="left" valign ="top"> <td align="left" id="r17c1-t2" headers="r1c1-t2">UNASSIGN_ATTRIBUTE_SET</td> <td align="left" headers="r17c1-t2 r1c2-t2">Brea ks the association between a column storing expressions and the attribute set.</td></tr> <tr align="left" valign="top"> <td align="le ft" id="r18c1-t2" headers="r1c1-t2">VALIDATE_EXPRESSIONS</td> <td align="left" headers="r18c1-t2 r1c2-t2">Validates expression metada ta and the expressions stored in a column.</td></tr> <tr align="left" valign="top"> <td align="left" id="r19c1-t2" headers="r1c1-t2"> XPINDEX_PARAMETERS</td> <td align="left" headers="r19c1-t2 r1c2-t2">Assigns XPath index parameters to an expression set.</td></tr></t body></table> <br /></div>  <a id="CJADGHBB" name="CJADGHBB"></a> <hr noshade="noshade" size="3" /> <div class ="refsect1"><a id="sthref272" name="sthref272"></a> <h2><font face="arial, helvetica, sans-se rif" color="#330099"><a id="sthref273" name="sthref273"></a><a id="sthref274" name="sthref274"></a>ADD_ELEMENTARY_ATTRIBUTE Procedure </font></h2> <p>This procedure adds the specified attribute to the attribute set.</p> <a id="sthref275" name="sthref275"></a> <p><st rong><font face="arial, helvetica, sans-serif" color="#330099">Format</font></strong></p> <pre xml:space="preserve">procedure ADD_ELE MENTARY_ATTRIBUTE ( attr_set IN VARCHAR2, --- attr set name attr_name IN VARCHAR2, --- attr name attr_type IN VARCHAR2); --- attr type --- or procedure ADD_ELEMENTARY_ATTRIBUTE ( attr_set IN VARCHAR2, --- attr set name attr_name IN VARCHAR2, --- table alias (name) tab_alias IN exf$table_alias); --- table alias for </pre> <a id="sthref276" name="sthref276"></a> <p><strong><font face ="arial, helvetica, sans-serif" color="#330099">Arguments</font></strong></p> <dl> <dt><font size="-1" face="arial, helvetica, sans-s erif"><strong>attr_set</strong></font></dt> <dd> <p>Name of the attribute set to which this attribute is added.</p></dd> <dt><font si ze="-1" face="arial, helvetica, sans-serif"><strong>attr_name</strong></font></dt> <dd> <p>Name of the elementary attribute to be add ed. No two attributes in a set can have the same name.</p></dd> <dt><font size="-1" face="arial, helvetica, sans-serif"><strong>attr_ type</strong></font></dt> <dd> <p>Datatype of the attribute. This argument accepts any standard SQL datatype or the name of an object type that is accessible to the current user.</p></dd> <dt><font size="-1" face="arial, helvetica, sans-serif"><strong>tab_alias</str ong></font></dt> <dd> <p>The type that identifies the database table to which the attribute is aliased.</p></dd></dl> <a id="sthref27 7" name="sthref277"></a> <p><strong><font face="arial, helvetica, sans-serif" color="#330099">Usage Notes</font></strong></p> <p>Thi s procedure adds an elementary attribute to an attribute set. If the attribute set was originally created from an existing object typ e, then additional attributes cannot be added.</p> <p>One or more, or all elementary attributes in an attribute set can be table alia ses. If an elementary attribute is a table alias, then the value assigned to the elementary attribute is a ROWID from the correspondi ng table. An attribute set with one or more table alias attributes cannot be created from an existing object type. For more informati on about table aliases, see <a href="app_a.htm#BGBDFBEG">Appendix A</a>.</p> <p>Elementary attributes cannot be added to an attribute set that is already assigned to a column storing expressions.</p> <p>See <a href="expressionconcepts.htm#CHDEGDBB">"Defining Attribu te Sets"</a> for more information about adding elementary attributes.</p> <p>Related views: USER_EXPFIL_ATTRIBUTE_SETS and USER_EXPFI L_ATTRIBUTES.</p> <a id="sthref278" name="sthref278"></a> <p><strong><font face="arial, helvetica, sans-serif" color="#330099">Examp les</font></strong></p> <p>The following commands add two elementary attributes to an attribute set:</p> <pre xml:space="preserve">BE GIN DBMS_EXPFIL.ADD_ELEMENTARY_ATTRIBUTE ( attr_set => 'HRAttrSet', attr_name => 'HRREP', attr_type => 'VARCHAR2(30)'); DBMS_EXPFIL.ADD_ELEMENTARY_ATTRIBUTE ( attr_set => 'HRAttrSet', attr_name => 'DEPT', tab_alias => exf$table_alias('DEPT')); END; / </pre> </div>  <a id="CJAJFGGD" name="CJAJFGGD "></a> <hr noshade="noshade" size="3" /> <div class="refsect1"><a id="sthref279" name="sthref 279"></a> <h2><font face="arial, helvetica, sans-serif" color="#330099"><a id="sthref280" name="sthref280"></a><a id="sthref281" nam e="sthref281"></a><a id="sthref282" name="sthref282"></a>ADD_FUNCTIONS Procedure</font></h2> <p>This procedure adds a user-defined fu nction, package, or type representing a set of functions to the attribute set.</p> <a id="sthref283" name="sthref283"></a> <p><stron g><font face="arial, helvetica, sans-serif" color="#330099">Format</font></strong></p> <pre xml:space="preserve">PROCEDURE ADD_FUNCTI ONS ( attr_set IN VARCHAR2, --- attr set name funcs_name IN VARCHAR2); --- function/package/t ype name </pre> <a id="sthref284" name="sthref284"></a> <p><strong><font face="arial, helvetica, sans-serif" color="#330099">Argumen ts</font></strong></p> <dl> <dt><font size="-1" face="arial, helvetica, sans-serif"><strong>attr_set</strong></font></dt> <dd> <p>Nam e of the attribute set to which the functions are added.</p></dd> <dt><font size="-1" face="arial, helvetica, sans-serif"><strong>fun cs_name</strong></font></dt> <dd> <p>Name of a function, package, or type (representing a function set) or its synonyms.</p></dd></dl > <a id="sthref285" name="sthref285"></a> <p><strong><font face="arial, helvetica, sans-serif" color="#330099">Usage Notes</font></s trong></p> <p>By default, an attribute set implicitly allows references to all Oracle supplied SQL functions for use by the expressio n set. If the expression set refers to a user-defined function, the expression set must be explicitly added to the attribute set.</p> <p>The ADD_FUNCTIONS procedure adds a user-defined function or a package (or type) representing a set of functions to the attribute set. Any new or modified expressions are validated using this list.</p> <p>The function or the package name can be specified with a s chema extension. If a function name is specified without a schema extension, only such references in the expression set are considere d valid. The expressions in a set can be restricted to use a synonym to a function or a package by adding the corresponding synonym t o the attribute set. This preserves the portability of the expression set to other schemas.</p> <p>See <a href="expressionconcepts.ht m#CHDEGDBB">"Defining Attribute Sets"</a> for more information about adding functions to an attribute set.</p> <p>Related views: USER _EXPFIL_ATTRIBUTE_SETS and USER_EXPFIL_ASET_FUNCTIONS</p> <a id="sthref286" name="sthref286"></a> <p><strong><font face="arial, helv etica, sans-serif" color="#330099">Examples</font></strong></p> <p>The following commands add two functions to the attribute set:</p> <pre xml:space="preserve">BEGIN DBMS_EXPFIL.ADD_FUNCTIONS (attr_set => 'Car4Sale', funcs_name => 'HorsePower'); DBMS_EXPFIL.ADD_FUNCTIONS (attr_set => 'Car4Sale', funcs_name => 'Scott. CrashTestRating'); END; / </pre> </div>  <a id="CJAFAGID" name="CJAFAGID"></a> <hr noshade="noshade" size ="3" /> <div class="refsect1"><a id="sthref287" name="sthref287"></a> <h2><font face="arial, helvetica, sans-serif" color="#330099"><a id="sthref288" name="sthref288"></a><a id="sthref289" name="sthref289"></a>ASSIGN_ATTRIBUTE _SET Procedure</font></h2> <p>This procedure assigns an attribute set to a <code>VARCHAR2</code> column in a user table to create an Expression column.</p> <a id="sthref290" name="sthref290"></a> <p><strong><font face="arial, helvetica, sans-serif" color="#330099"> Format</font></strong></p> <pre xml:space="preserve">PROCEDURE ASSIGN_ATTRIBUTE_SET ( attr_set IN VARCHAR2, --- attr set name expr_tab IN VARCHAR2, --- name of the table expr_col IN VARCHAR2, --- exp col umn in the table force IN VARCHAR2 --- to use existing expressions default 'FALSE' ); </pre> <a id="sthref291" name="sthref291"></a> <p><strong><font face="arial, helvetica, sans-serif" color="#330099">Arguments</fo nt></strong></p> <dl> <dt><font size="-1" face="arial, helvetica, sans-serif"><strong>attr_set</strong></font></dt> <dd> <p>The name of the attribute set.</p></dd> <dt><font size="-1" face="arial, helvetica, sans-serif"><strong>expr_tab</strong></font></dt> <dd> <p> The table storing the expression set.</p></dd> <dt><font size="-1" face="arial, helvetica, sans-serif"><strong>expr_col</strong></fon t></dt> <dd> <p>The column in the table that stores the expressions.</p></dd> <dt><font size="-1" face="arial, helvetica, sans-serif" ><strong>force</strong></font></dt> <dd> <p>Argument used to trust the existing expressions in a table (and skip validation).</p></dd ></dl> <a id="sthref292" name="sthref292"></a> <p><strong><font face="arial, helvetica, sans-serif" color="#330099">Usage Notes</fon t></strong></p> <p>The ASSIGN_ATTRIBUTE_SET procedure assigns an attribute set to a <code>VARCHAR2</code> column in a user table to c reate an Expression column. The attribute set contains the elementary attribute names and their datatypes and any functions used in t he expressions. The attribute set is used by the Expression column to validate changes and additions to the expression set.</p> <p>An attribute set can be assigned only to a table column in the same schema as the attribute set. An attribute set can be assigned to on e or more table columns. Assigning an attribute set to a column storing expressions implicitly creates methods for the associated obj ect type. For this operation to succeed, the object type cannot have any dependent objects before the attribute set is assigned.</p> <p>By default, the column should not have any expressions at the time of association. However, if the values in the column are known to be valid expressions, you can use a value of <code>'TRUE'</code> for the <code>force</code> argument to assign the attribute set t o a column containing expressions.</p> <p>See <a href="expressionconcepts.htm#BABGCHJB">"Defining Expression Columns"</a> for more in formation about adding elementary attributes.</p> <p>Related views: USER_EXPFIL_ATTRIBUTE_SETS and USER_EXPFIL_EXPRESSION_SETS</p> <a id="sthref293" name="sthref293"></a> <p><strong><font face="arial, helvetica, sans-serif" color="#330099">Examples</font></strong>< /p> <p>The following command assigns the attribute set to a column storing expressions. The expression set should be empty at the tim e of association.</p> <pre xml:space="preserve">BEGIN DBMS_EXPFIL.ASSIGN_ATTRIBUTE_SET (attr_set => 'Car4Sale', expr_tab => 'Consumer', expr_col => 'Interest'); END; / </pre> </div >  <a id="CJADCEED" name="CJADCEED"></a> <hr noshade="noshade" size="3" /> <div class="refsect1"><a id="sthref294" name="sthref294"></a> <h2><font face="arial, helvetica, sans-serif" color="#330099">< a id="sthref295" name="sthref295"></a><a id="sthref296" name="sthref296"></a>BUILD_EXCEPTIONS_TABLE Procedure</font></h2> <p>This pro cedure creates the exception table, used in validation, in the current schema.</p> <a id="sthref297" name="sthref297"></a> <p><stron g><font face="arial, helvetica, sans-serif" color="#330099">Format</font></strong></p> <pre xml:space="preserve">PROCEDURE BUILD_EXCE PTIONS_TABLE ( exception_tab IN VARCHAR2); -- exception table to be created -- </pre> <a id="sthref298" name="sthref2 98"></a> <p><strong><font face="arial, helvetica, sans-serif" color="#330099">Arguments</font></strong></p> <dl> <dt><font size="-1" face="arial, helvetica, sans-serif"><strong>exception_tab</strong></font></dt> <dd> <p>Name of the exception table.</p></dd></dl> <a id="sthref299" name="sthref299"></a> <p><strong><font face="arial, helvetica, sans-serif" color="#330099">Usage Notes</font></strong ></p> <p>The expressions stored in a table column can be validated using the VALIDATE_EXPRESSIONS procedure. During expression valida tion, you can optionally provide the name of the exception table in which the references to the invalid expressions are stored. The B UILD_EXCEPTIONS_TABLE procedure creates the exception table in the current schema.</p> <p>See <a href="expressionconcepts.htm#CHDBBCD H">"Evaluation Semantics"</a> and <a href="#CJAHCFCA">"VALIDATE_EXPRESSIONS Procedure"</a> for more information.</p> <p>Related view: USER_TABLES</p> <a id="sthref300" name="sthref300"></a> <p><strong><font face="arial, helvetica, sans-serif" color="#330099">Exampl es</font></strong></p> <p>The following command creates the exception table, <code>InterestExceptions</code>, in the current schema:< /p> <pre xml:space="preserve">BEGIN DBMS_EXPFIL.BUILD_EXCEPTIONS_TABLE ( exception_tab => 'Interes tExceptions'); END; / </pre> </div>  <hr noshade="noshade" size="3" /> <div class="refsect1"><a id="sthref301" name="sthref301"></a> <h2><font face="arial, helvetica, sans-serif" color="#330099"><a i d="sthref302" name="sthref302"></a><a id="sthref303" name="sthref303"></a>CLEAR_EXPRSET_STATS Procedure</font></h2> <p>This procedure clears the predicate statistics for the expression set stored in a table column.</p> <a id="sthref304" name="sthref304"></a> <p><st rong><font face="arial, helvetica, sans-serif" color="#330099">Format</font></strong></p> <pre xml:space="preserve">PROCEDURE CLEAR_E XPRSET_STATS ( expr_tab IN VARCHAR2, --- table storing expression set expr_col IN VARCHAR2); --- column in the table with set </pre> <a id="sthref305" name="sthref305"></a> <p><strong><font face="arial, helvetica, sans-serif " color="#330099">Arguments</font></strong></p> <dl> <dt><font size="-1" face="arial, helvetica, sans-serif"><strong>exp_tab</strong> </font></dt> <dd> <p>The table storing the expression set.</p></dd> <dt><font size="-1" face="arial, helvetica, sans-serif"><strong>e xpr_col</strong></font></dt> <dd> <p>The column in the table that stores the expressions.</p></dd></dl> <a id="sthref306" name="sthre f306"></a> <p><strong><font face="arial, helvetica, sans-serif" color="#330099">Usage Notes</font></strong></p> <p>This procedure cl ears the predicate statistics for the expression set stored in a table column. See also <a href="#CIHIJFEE">"GET_EXPRSET_STATS Proced ure"</a> for information about gathering the statistics.</p> <p>Related views: USER_EXPFIL_EXPRESSION_SETS and USER_EXPFIL_EXPRSET_ST ATS</p> <a id="sthref307" name="sthref307"></a> <p><strong><font face="arial, helvetica, sans-serif" color="#330099">Examples</font> </strong></p> <p>The following command clears the predicate statistics for the expression set stored in <code>Interest</code> column of the <code>Consumer</code> table:</p> <pre xml:space="preserve">BEGIN DBMS_EXPFIL.CLEAR_EXPRSET_STATS (expr_tab => 'Consumer', expr_col => 'Interest'); END; / </pre> </div>  <a id="CJACBAGI" na me="CJACBAGI"></a> <hr noshade="noshade" size="3" /> <div class="refsect1"><a id="sthref308" name="sthref308"></a> <h2><font face="arial, helvetica, sans-serif" color="#330099"><a id="sthref309" name="sthref309"></a><a id="st href310" name="sthref310"></a>COPY_ATTRIBUTE_SET Procedure</font></h2> <p>This procedure copies an attribute set along with its user- defined function list and default index parameters to another set.</p> <a id="sthref311" name="sthref311"></a> <p><strong><font face ="arial, helvetica, sans-serif" color="#330099">Format</font></strong></p> <pre xml:space="preserve">PROCEDURE COPY_ATTRIBUTE_SET ( from_set IN VARCHAR2, --- name of an existing att set to_set IN VARCHAR2); --- new set name < /pre> <a id="sthref312" name="sthref312"></a> <p><strong><font face="arial, helvetica, sans-serif" color="#330099">Arguments</font>< /strong></p> <dl> <dt><font size="-1" face="arial, helvetica, sans-serif"><strong>from_set</strong></font></dt> <dd> <p>Name of an ex isting attribute set to be copied.</p></dd> <dt><font size="-1" face="arial, helvetica, sans-serif"><strong>to_set</strong></font></d t> <dd> <p>Name of the new attribute set.</p></dd></dl> <a id="sthref313" name="sthref313"></a> <p><strong><font face="arial, helvet ica, sans-serif" color="#330099">Usage Notes</font></strong></p> <p>A schema-extended name can be used for the <code>from_set</code> argument to copy an attribute set across schemas. The user issuing the command must have <code>EXECUTE</code> privileges for the obje ct type associated with the original attribute set. The user must ensure that any references to schema objects (user-defined function s, tables, and embedded objects) are valid in the new schema.</p> <p>The default index parameters and the user-defined function list of the new set can be changed independent of the original set.</p> <p>Related views: ALL_EXPFIL_ATTRIBUTE_SETS and ALL_EXPFIL_ATTRIBU TES.</p> <a id="sthref314" name="sthref314"></a> <p><strong><font face="arial, helvetica, sans-serif" color="#330099">Examples</font ></strong></p> <p>The following command makes a copy of the Car4Sale attribute set:</p> <pre xml:space="preserve">BEGIN DBMS_EXPFIL .COPY_ATTRIBUTE_SET (from_set => 'Car4Sale', to_set => 'Vehicle'); END; / </pre> </div>  <a id="CJAFFDBB" name="CJAFFDBB"></a> <hr noshade="noshade" size="3" /> <div class="refsect1"><a id="sthref315" name="sthref315"></a> <h2><font face="arial, helvetica, sans-serif" color="#330099"><a id="sthref316" name="sthref316"></a><a id="sthref317" name="sthref317"></a>CREATE_ATTRIBUTE_SET Procedure</font></h2> <p>This procedu re creates an empty attribute set or an attribute set with a complete set of elementary attributes derived from an object type with a matching name.</p> <a id="sthref318" name="sthref318"></a> <p><strong><font face="arial, helvetica, sans-serif" color="#330099">For mat</font></strong></p> <pre xml:space="preserve">PROCEDURE CREATE_ATTRIBUTE_SEt ( attr_set IN VARCHAR2, --- att r set name from_type IN VARCHAR2 --- object type for attributes default 'NO'); </pre> <a id="sthref319" name="sthref319"></a> <p><strong><font face="arial, helvetica, sans-serif" color="#330099">Arguments</font></strong ></p> <dl> <dt><font size="-1" face="arial, helvetica, sans-serif"><strong>attr_set</strong></font></dt> <dd> <p>The name of the attr ibute set to be created.</p></dd> <dt><font size="-1" face="arial, helvetica, sans-serif"><strong>from_type</strong></font></dt> <dd> <p><code>YES</code>, if the attributes for the attribute set should be derived from an existing object type.</p></dd></dl> <a id="st href320" name="sthref320"></a> <p><strong><font face="arial, helvetica, sans-serif" color="#330099">Usage Notes</font></strong></p> <p>The object type used for an attribute set cannot contain any user methods, and it should not be an evolved type (with the use of < code>ALTER TYPE</code> command). This object type should not have any dependent objects at the time of the attribute set creation. If the attribute set is not derived from an existing object type, this procedure creates an object type with a matching name.</p> <p>An attribute set with one or more table alias attributes cannot be derived from an object type. For this purpose, create an empty attri bute set and add one elementary attribute at a time using the DBMS_EXPFIL.ADD_ELEMENTARY_ATTRIBUTE procedure. (See <a href="app_a.htm #BGBDFBEG">Appendix A</a> for more information.)</p> <p>See <a href="expressionconcepts.htm#CHDEGDBB">"Defining Attribute Sets"</a> a nd <a href="#CJADGHBB">"ADD_ELEMENTARY_ATTRIBUTE Procedure"</a> for more information.</p> <p>Related views: USER_EXPFIL_ATTRIBUTE_SET and USER_EXPFIL_ATTRIBUTES.</p> <a id="sthref321" name="sthref321"></a> <p><strong><font face="arial, helvetica, sans-serif" color= "#330099">Examples</font></strong></p> <p>The following commands create an attribute set with all the required elementary attributes derived from the <code>Car4Sale</code> type:</p> <pre xml:space="preserve">CREATE OR REPLACE TYPE Car4Sale AS OBJECT (Model VARCHAR2(20), Year NUMBER, Price NUMBER, Mileage NUMBER); / BEGIN DBMS_EXPFIL.CREATE_ATTRIBUTE_SET(attr_set => 'Car4Sale', from_type => 'YES'); END; / </pre> <p>Assuming that the <code>Car4Sale</code> t ype does not exist, the attribute set can be created from scratch as shown in the following example:</p> <pre xml:space="preserve">BE GIN DBMS_EXPFIL.CREATE_ATTRIBUTE_SET(attr_set => 'Car4Sale'); DBMS_EXPFIL.ADD_ELEMENTARY_ATTRIBUTE( attr_set => 'Car4Sale', attr_name => 'Model', attr_type =& gt; 'VARCHAR2(20)'); DBMS_EXPFIL.ADD_ELEMENTARY_ATTRIBUTE( attr_set => 'Car4Sale', attr_name => 'Year', attr_type => 'NUMBER'); DBMS_EXPFIL.ADD_ELEMENTARY_ATTR IBUTE( attr_set => 'Car4Sale', attr_name => 'Price', attr_type => 'NUMBER'); DBMS_EXPFIL.ADD_ELEMENTARY_ATTRIBUTE( attr_set => 'Car4Sale', attr_name => 'Mileage', attr_type => 'NUMBER'); END; / </pre> </div>  <a id="CJAEHGAH" name="CJAEHGAH"></a> <hr noshade="noshade" size="3" /> <div class="ref sect1"><a id="sthref322" name="sthref322"></a> <h2><font face="arial, helvetica, sans-serif" color="#330099"><a id="sthref323" name="sthref323"></a><a id="sthref324" name="sthref324"></a>DEFAULT_INDEX_PARAMETERS Procedure</fon t></h2> <p>This procedure assigns default index parameters to an attribute set. It also adds or drops a partial list of stored and in dexed attributes to or from the default list associated with the attribute list.</p> <a id="sthref325" name="sthref325"></a> <p><str ong><font face="arial, helvetica, sans-serif" color="#330099">Format</font></strong></p> <pre xml:space="preserve">PROCEDURE DEFAULT_ INDEX_PARAMETERS ( attr_set IN VARCHAR2, --- attribute set name attr_list IN EXF$ATTRIBUTE_LIST , --- stored and indexed attributes operation IN VARCHAR2 --- to ADD or DROP default 'ADD'); </pre> <a id="sthref326" name="sthref326"></a> <p><strong><font face="arial, helvetica, sans-serif" color="#330099">Arguments</font></strong></p> <dl> <dt><font size="-1" face="arial, helvetica, sans-serif"><st rong>attr_set</strong></font></dt> <dd> <p>The name of the attribute set.</p></dd> <dt><font size="-1" face="arial, helvetica, sans-s erif"><strong>attr_list</strong></font></dt> <dd> <p>An instance of <code>EXF$ATTRIBUTE_LIST</code> with a partial list of (default) stored and indexed attributes for an Expression Filter index.</p></dd> <dt><font size="-1" face="arial, helvetica, sans-serif"><stron g>operation</strong></font></dt> <dd> <p>The operation to be performed on the list of index parameters. Default value: <code>ADD</cod e>. Valid values: <code>ADD</code> and <code>DROP</code>.</p></dd></dl> <a id="sthref327" name="sthref327"></a> <p><strong><font fac e="arial, helvetica, sans-serif" color="#330099">Usage Notes</font></strong></p> <p>Existing Expression Filter indexes are not modifi ed when the default parameters for the corresponding attribute set are changed. The new index defaults are used when a new Expression Filter index is created and when an existing index is rebuilt. (See <a href="sql.htm#CHDBGGJB">ALTER INDEX REBUILD</a> in <a href="s ql.htm#CHDDCAJI">Chapter 6</a> for more information about rebuilding indexes.)</p> <p>See <a href="indexexpressions.htm#CJAIEDCF">"Cr eating an Index from Default Parameters"</a> for more information about assigning default index parameters to an attribute set.</p> < p>Related views: USER_EXPFIL_ATTRIBUTE_SETS and USER_EXPFIL_DEF_INDEX_PARAMS</p> <a id="sthref328" name="sthref328"></a> <p><strong> <font face="arial, helvetica, sans-serif" color="#330099">Examples</font></strong></p> <p>The following command adds the specified st ored and indexed attributes to the attribute set's default index parameters list:</p> <pre xml:space="preserve">BEGIN DBMS_EXPFIL.DE FAULT_INDEX_PARAMETERs( attr_set => 'Car4Sale', attr_list => exf$attribute_list ( exf$attribute (attr_name =&g t; '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'), exf$attribute (att r_name => 'CrashTestRating(Model, Year)', attr_oper => exf$indexoper('=','<','>','>=','<='), attr_indexed => 'FALSE')), operation => 'ADD'); END; / </pre> <p>The following command drops the <code>CrashTestRating(Model, Year)</code> attribute (stored or indexed) from the previous list.</p> <pre xml:space="preserve">BEGIN DBMS_EXPFIL.DEFAULT_INDEX_PARAMETERS( attr_set => 'Car4Sale', attr_list => exf$attribute_list ( exf$at tribute (attr_name => 'CrashTestRating(Model, Year)')), operation => 'DROP'); END; / </pre> </div>  <a id="CJAIADEG" name="CJAIADEG"></a> <hr noshade="noshade" size="3" /> <div class="refsect1"><a id="sthref329" name="sthref329"></a> <h2><font face="arial, helvetica, sans-serif" color="#330099"><a id="sthref330" name= "sthref330"></a><a id="sthref331" name="sthref331"></a>DEFAULT_XPINDEX_PARAMETERS Procedure</font></h2> <p><a id="sthref332" name="st href332"></a><a id="sthref333" name="sthref333"></a>This procedure adds (or drops) a partial list of XPath parameters to the default index parameters associated with the attribute set.</p> <a id="sthref334" name="sthref334"></a> <p><strong><font face="arial, helvet ica, sans-serif" color="#330099">Format</font></strong></p> <pre xml:space="preserve">PROCEDURE DEFAULT_XPINDEX_PARAMETERS ( attr_set IN VARCHAR2, --- attribute set name xmlt_attr IN VARCHAR2, --- XMLType attrubue name xptag_list IN EXF$XPATH_TAGS, --- XPath tags for index operation IN VARCHAR2 --- to ADD or DROP default 'ADD'); </pre> <a id="sthref335" name="sthref335"></a> < p><strong><font face="arial, helvetica, sans-serif" color="#330099">Arguments</font></strong></p> <dl> <dt><font size="-1" face="aria l, helvetica, sans-serif"><strong>attr_set</strong></font></dt> <dd> <p>The name of the attribute set.</p></dd> <dt><font size="-1" f ace="arial, helvetica, sans-serif"><strong>xmlt_attr</strong></font></dt> <dd> <p>The name of the attribute with the <code>XMLType</c ode> datatype.</p></dd> <dt><font size="-1" face="arial, helvetica, sans-serif"><strong>xptag_list</strong></font></dt> <dd> <p>An in stance of <code>EXF$XPATH_TAGS</code> type with a partial list of XML elements and attributes to be configured for the Expression Fil ter index.</p></dd> <dt><font size="-1" face="arial, helvetica, sans-serif"><strong>operation</strong></font></dt> <dd> <p>The operat ion to be performed on the list of index parameters. Default value: <code>ADD</code>. Valid values: <code>ADD</code> and <code>DROP</ code>.</p></dd></dl> <a id="sthref336" name="sthref336"></a> <p><strong><font face="arial, helvetica, sans-serif" color="#330099">Us age Notes</font></strong></p> <p>The attribute set used for an expression set may have one or more XML type attributes (defined with <code>XMLType</code> datatype) and the corresponding expressions may contain XPath predicates on these attributes. The Expression Fil ter index created for the expression set can be tuned to process these XPath predicates efficiently by using some XPath-specific inde x parameters (in addition to some non-XPath index parameters).</p> <p>The DEFAULT_XPINDEX_PARAMETERS procedure adds (or drops) a part ial list of XPath parameters to the default index parameters associated with the attribute set. The XPath parameters are assigned to a specific <code>XMLType</code> attribute in the attribute set and this information can be viewed using the USER_EXPFIL_DEF_INDEX_PAR AMS view. The DEFAULT_INDEX_PARAMETERS procedure and the DEFAULT_XPINDEX_PARAMETERS procedure can be used independent of each other. They maintain a common list of default index parameters for the attribute set.</p> <p>See <a href="xpathpredicates.htm#CIHDBFHG">"Ind ex Tuning for XPath Predicates"</a> for more information about XPath parameters to the default index parameters of an attribute set. See also <a href="#CJAEHGAH">"DEFAULT_INDEX_PARAMETERS Procedure"</a> for more information about default index parameters.</p> <p>Rel ated views: USER_EXPFIL_ATTRIBUTES and USER_EXPFIL_DEF_INDEX_PARAMS.</p> <div align="center"> <div class="inftblinformal"><br /> <tab le class="informal" summary="This is a layout table to format a note" title="This is a layout table to format a note" dir="ltr" borde r="1" width="80%" frame="hsides" rules="groups" cellpadding="3" cellspacing="0"> <tbody> <tr align="left" valign="top"> <td align="le ft"> <p><font face="arial, helvetica, sans-serif"><strong>Note:</strong></font></p> The values assigned to the <code>tag_name</code> argument of <code>exf$xpath_tag</code> type are case sensitive.</td></tr></tbody></table> <br /></div>  </div> <a id="sthref337" name="sthref337"></a> <p><strong><font face="arial, helvetica, sans-serif" color="#330099">Examples</font> </strong></p> <p>The following command adds the specified XML tags to the default index parameters list along with their preferences such as positional or value filter and indexed or stored predicate group:</p> <pre xml:space="preserve">BEGIN DBMS_EXPFIL.DEFAULT_X PINDEX_PARAMETERS( attr_set => 'Car4Sale', xmlt_attr => 'Details', xptag_list => --- XPath tag list exf$xpath_tags( exf$xpath_tag(tag_name => 'stereo@make', --- XML attri bute 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)'), --- val ue filter exf$xpath_tag(tag_name => 'GPS', tag_indexed => 'TRUE', tag_type => null) ) ); END; / </pre> <p>The following command drops the <code>stereo@make</code> tag fr om the default index parameters:</p> <pre xml:space="preserve">BEGIN DBMS_EXPFIL.DEFAULT_XPINDEX_PARAMETERS( attr_set => ; 'Car4Sale', xmlt_attr => 'Details', xptag_list => --- XPath tag list exf$xpath_tags( exf$xpath_tag(tag_name => 'stereo@make') ), operation => 'DROP' ); END ; / </pre> </div>  <a id="CJAFJDEB" name="CJAFJDEB"></a> <hr noshade="noshade" size="3" /> <div class="re fsect1"><a id="sthref338" name="sthref338"></a> <h2><font face="arial, helvetica, sans-serif" color="#330099"><a id="sthref339" name="sthref339"></a><a id="sthref340" name="sthref340"></a>DEFRAG_INDEX Procedure</font></h2> <p> This procedure rebuilds the bitmap indexes online and thus reduces the fragmentation.</p> <a id="sthref341" name="sthref341"></a> <p ><strong><font face="arial, helvetica, sans-serif" color="#330099">Format</font></strong></p> <pre xml:space="preserve">PROCEDURE DEF RAG_INDEX ( idx_name IN VARCHAR2); --- expfil index to defrag </pre> <a id="sthref342" name="sthref342"></a> <p> <strong><font face="arial, helvetica, sans-serif" color="#330099">Arguments</font></strong></p> <dl> <dt><font size="-1" face="arial, helvetica, sans-serif"><strong>idx_name</strong></font></dt> <dd> <p>The name of the Expression Filter index.</p></dd></dl> <a id="s thref343" name="sthref343"></a> <p><strong><font face="arial, helvetica, sans-serif" color="#330099">Usage Notes</font></strong></p> <p>The bitmap indexes defined for the indexed attributes of an Expression Filter index become fragmented as additions and updates ar e made to the expression set. The DEFRAG_INDEX procedure rebuilds the bitmap indexes online and thus reduces the fragmentation.</p> < p>Indexes can be defragmented when the expression set is being modified. However, you should schedule defragmentation when the worklo ad is relatively light.</p> <p>See <a href="indexexpressions.htm#CIHGADEC">"Index Storage and Maintenance"</a> for more information a bout rebuilding indexes.</p> <p>Related views: USER_EXPFIL_INDEXES and USER_INDEXES.</p> <a id="sthref344" name="sthref344"></a> <p> <strong><font face="arial, helvetica, sans-serif" color="#330099">Examples</font></strong></p> <p>The following command is issued to defragment the bitmap indexes associated with the Expression Filter index:</p> <pre xml:space="preserve">BEGIN DBMS_EXPFIL.DEFRAG_I NDEX (idx_name => 'InterestIndex'); END; / </pre> </div>  <a id="CJAHJFAD" name="CJAHJFAD"></a> <hr no shade="noshade" size="3" /> <div class="refsect1"><a id="sthref345" name="sthref345"></a> <h2 ><font face="arial, helvetica, sans-serif" color="#330099"><a id="sthref346" name="sthref346"></a><a id="sthref347" name="sthref347"> </a><a id="sthref348" name="sthref348"></a>DROP_ATTRIBUTE_SET Procedure</font></h2> <p>This procedure drops an attribute set not bein g used for any expression set.</p> <a id="sthref349" name="sthref349"></a> <p><strong><font face="arial, helvetica, sans-serif" colo r="#330099">Format</font></strong></p> <pre xml:space="preserve">PROCEDURE DROP_ATTRIBUTE_SET ( attr_set IN VARCHAR 2); --- attr set name </pre> <a id="sthref350" name="sthref350"></a> <p><strong><font face="arial, helvetica, sans-serif" color="# 330099">Arguments</font></strong></p> <dl> <dt><font size="-1" face="arial, helvetica, sans-serif"><strong>attr_set</strong></font></ dt> <dd> <p>The name of the attribute set to be dropped.</p></dd></dl> <a id="sthref351" name="sthref351"></a> <p><strong><font face ="arial, helvetica, sans-serif" color="#330099">Usage Notes</font></strong></p> <p>The DROP_ATTRIBUTE_SET procedure drops an attribut e set not being used for any expression set. If the attribute set was initially created from an existing object type, the object type remains after dropping the attribute set. Otherwise, the object type is dropped with the attribute set.</p> <p>Related views: USER_E XPFIL_ATTRIBUTE_SETS and USER_EXPFIL_EXPRESSION_SETS.</p> <a id="sthref352" name="sthref352"></a> <p><strong><font face="arial, helv etica, sans-serif" color="#330099">Examples</font></strong></p> <p>Assuming that the attribute set is not used by an Expression colum n, the following command drops the attribute set:</p> <pre xml:space="preserve">BEGIN DBMS_EXPFIL.DROP_ATTRIBUTE_SET(attr_set => 'Car4Sale'); END; / </pre> </div>  <a id="CIHIJFEE" name="CIHIJFEE"></a> <hr noshade="noshade" size="3" /> <div class="refsect1"><a id="sthref353" name="sthref353"></a> <h2><font face="arial, helve tica, sans-serif" color="#330099"><a id="sthref354" name="sthref354"></a><a id="sthref355" name="sthref355"></a>GET_EXPRSET_STATS Pro cedure</font></h2> <p>This procedure computes the predicate statistics for an expression set and stores them in the expression filter dictionary.</p> <a id="sthref356" name="sthref356"></a> <p><strong><font face="arial, helvetica, sans-serif" color="#330099">Format </font></strong></p> <pre xml:space="preserve">PROCEDURE GET_EXPRSET_STATS ( expr_tab IN VARCHAR2, --- table sto ring expression set expr_col IN VARCHAR2); --- column in the table with set </pre> <a id="sthref357" name="sthre f357"></a> <p><strong><font face="arial, helvetica, sans-serif" color="#330099">Arguments</font></strong></p> <dl> <dt><font size="-1 " face="arial, helvetica, sans-serif"><strong>expr_tab</strong></font></dt> <dd> <p>The table storing the expression set.</p></dd> <d t><font size="-1" face="arial, helvetica, sans-serif"><strong>expr_col</strong></font></dt> <dd> <p>The column in the table that stor es the expressions.</p></dd></dl> <a id="sthref358" name="sthref358"></a> <p><strong><font face="arial, helvetica, sans-serif" color ="#330099">Usage Notes</font></strong></p> <p>When a representative set of expressions are stored in a table column, you can use pred icate statistics for those expressions to configure the corresponding Expression Filter index (using the <code>TOP</code> parameters clause). The GET_EXPRSET_STATS procedure computes the predicate statistics for an expression set and stores them in the expression fi lter dictionary.</p> <p>See <a href="indexexpressions.htm#CJAGAJCA">"Creating an Index from Statistics"</a> for more information abou t using predicate statistics.</p> <p>Related views: USER_EXPFIL_EXPRESSION_SETS and USER_EXPFIL_EXPRSET_STATS.</p> <a id="sthref359" name="sthref359"></a> <p><strong><font face="arial, helvetica, sans-serif" color="#330099">Examples</font></strong></p> <p>The follo wing command computes the predicate statistics for the expressions stored in the <code>Interest</code> column of the <code>Consumer</ code> table:</p> <pre xml:space="preserve">BEGIN DBMS_EXPFIL.GET_EXPRSET_STATS (expr_tab => 'Consumer', expr_col => 'Interest'); END; / </pre> </div>  <a id="CJABDFBH" name="CJABDFBH"></a> <hr noshad e="noshade" size="3" /> <div class="refsect1"><a id="sthref360" name="sthref360"></a> <h2><fo nt face="arial, helvetica, sans-serif" color="#330099"><a id="sthref361" name="sthref361"></a><a id="sthref362" name="sthref362"></a> <a id="sthref363" name="sthref363"></a>GRANT_PRIVILEGE Procedure</font></h2> <p>This procedure grants privileges on one or more Expre ssion columns to other users.</p> <a id="sthref364" name="sthref364"></a> <p><strong><font face="arial, helvetica, sans-serif" color ="#330099">Format</font></strong></p> <pre xml:space="preserve">PROCEDURE GRANT_PRIVILEGE ( expr_tab IN VARCHAR2, --- table w/ the expr column expr_col IN VARCHAR2, --- column storing the expressions priv_type IN VARCHAR2, --- type of priv to be granted to_user IN VARCHAR2); --- user to which the priv is --- granted </pre> <a id="sthref365" name="sthref365"></a> <p><strong><font face="arial, helvetic a, sans-serif" color="#330099">Arguments</font></strong></p> <dl> <dt><font size="-1" face="arial, helvetica, sans-serif"><strong>exp r_tab</strong></font></dt> <dd> <p>The table storing the expression set.</p></dd> <dt><font size="-1" face="arial, helvetica, sans-se rif"><strong>expr_col</strong></font></dt> <dd> <p>The column in the table that stores the expressions.</p></dd> <dt><font size="-1" face="arial, helvetica, sans-serif"><strong>priv_type</strong></font></dt> <dd> <p>Types of the privilege to be granted. Valid values : INSERT EXPRESSION, UPDATE EXPRESSION, ALL</p></dd> <dt><font size="-1" face="arial, helvetica, sans-serif"><strong>to_user</strong> </font></dt> <dd> <p>User to which the privilege is granted.</p></dd></dl> <a id="sthref366" name="sthref366"></a> <p><strong><font face="arial, helvetica, sans-serif" color="#330099">Usage Notes</font></strong></p> <p>The SQL <code>EVALUATE</code> operator evaluat es expressions with the privileges of the owner of the table that stores the expressions. The privileges of the user issuing the quer y are not considered. The owner of the table can insert, update, and delete expressions. Other users must have <code>INSERT</code> an d <code>UPDATE</code> privileges for the table and <code>INSERT EXPRESSION</code> and <code>UPDATE EXPRESSION</code> privilege for a specific Expression column in the table.</p> <p>Using the GRANT_PRIVILEGE procedure, the owner of the table can grant <code>INSERT</c ode> <code>EXPRESSION</code> or <code>UPDATE</code> <code>EXPRESSION</code> privileges on one or more Expression columns to other use rs. Both the privileges can be granted to a user by specifying <code>ALL</code> for the privilege type.</p> <p>See <a href="#CJABIGJJ ">"REVOKE_PRIVILEGE Procedure"</a> and <a href="expressionconcepts.htm#CHDBDBHG">"Granting and Revoking Privileges"</a> for more info rmation about granting and revoking privileges.</p> <p>Related views: USER_EXPFIL_EXPRESSION_SETS and USER_EXPFIL_PRIVILEGES.</p> <a id="sthref367" name="sthref367"></a> <p><strong><font face="arial, helvetica, sans-serif" color="#330099">Examples</font></strong></ p> <p>The owner of <code>Consumer</code> table can grant <code>INSERT</code> <code>EXPRESSION</code> privileges to user <code>SCOTT</ code> with the following command. User <code>SCOTT</code> should also have <code>INSERT</code> privileges on the table so that he can add new expressions to the set.</p> <pre xml:space="preserve">BEGIN DBMS_EXPFIL.GRANT_PRIVILEGE (expr_tab => 'Consumer', expr_col => 'Interest', priv_type => 'INSERT EXPRESSION', to_user => 'SCOTT'); END; / </pre> </div>  <a id="CJAEGGHA" name="CJAEGGHA"></a> <hr noshade="noshade" size="3" /> <div class="refsect1"><a id="sthref368" name="sthref368"></ a> <h2><font face="arial, helvetica, sans-serif" color="#330099"><a id="sthref369" name="sthref369"></a><a id="sthref370" name="sthr ef370"></a>INDEX_PARAMETERS Procedure</font></h2> <p>This procedure fine-tunes the index parameters for each expression set before in dex creation.</p> <a id="sthref371" name="sthref371"></a> <p><strong><font face="arial, helvetica, sans-serif" color="#330099">Forma t</font></strong></p> <pre xml:space="preserve">PROCEDURE INDEX_PARAMETERS ( expr_tab IN VARCHAR2, --- table wit h expr column expr_col IN VARCHAR2, --- column storing expressions attr_list IN EXF$ATTRIBUTE_L IST, operation IN VARCHAR2 --- type of operation default 'ADD'); </pre> <a id=" sthref372" name="sthref372"></a> <p><strong><font face="arial, helvetica, sans-serif" color="#330099">Arguments</font></strong></p> < dl> <dt><font size="-1" face="arial, helvetica, sans-serif"><strong>expr_tab</strong></font></dt> <dd> <p>The table storing the expre ssion set.</p></dd> <dt><font size="-1" face="arial, helvetica, sans-serif"><strong>expr_col</strong></font></dt> <dd> <p>The column in the table that stores the expressions.</p></dd> <dt><font size="-1" face="arial, helvetica, sans-serif"><strong>attr_list</strong> </font></dt> <dd> <p>An instance of <code>EXF$ATTRIBUTE_LIST</code> with a partial list of stored and indexed attributes.</p></dd> <d t><font size="-1" face="arial, helvetica, sans-serif"><strong>operation</strong></font></dt> <dd> <p>The operation to be performed on the list of index parameters. Default value: <code>ADD</code>. Valid values: <code>ADD</code>, <code>DROP</code>, <code>DEFAULT</cod e>, and <code>CLEAR</code>.</p></dd></dl> <a id="sthref373" name="sthref373"></a> <p><strong><font face="arial, helvetica, sans-seri f" color="#330099">Usage Notes</font></strong></p> <p>An attribute set can be used by multiple expression sets stored in different co lumns of user tables. By default, the index parameters associated with the attribute set are used to define an Expression Filter inde x on an expression set. If you need to fine-tune the index for each expression set, you can specify a small list of the index paramet ers in the <code>PARAMETERS</code> clause of the <code>CREATE</code> <code>INDEX</code> statement. However, when an Expression Filter index uses a large number of index parameters or if the index is configured for XPath predicates, fine-tuning the parameters with th e <code>CREATE</code> <code>INDEX</code> statement is not possible.</p> <p>The INDEX_PARAMETERS procedure fine-tunes the index parame ters for each expression set before index creation. This procedure can be used to copy the defaults from the corresponding attribute set and selectively add (or drop) additional index parameters for the expression set. (You use the XPINDEX_PARAMETERS procedure to ad d and drop XPath index parameters.) The Expression Filter index defined for an expression set with a non-empty list of index paramete rs always uses these parameters. The INDEX_PARAMETERS procedure cannot be used when the Expression Filter index is already defined fo r the column storing expressions.</p> <p>The operations allowed with this procedure include:</p> <ul> <li type="disc"> <p>Deriving th e current list of default index parameters (including any XPath-specific parameters) from the corresponding attribute set and assigni ng them to the specified expression set (a value of <code>DEFAULT</code> for the operation argument).</p></li> <li type="disc"> <p>Ad ding (or dropping) one or more attributes to (or from) the current list of parameters assigned to the expression set (values of <code >ADD</code> or <code>DROP</code> for the operation argument).</p></li> <li type="disc"> <p>Clearing the index parameters assigned to the expression set. This enables the user to start using default parameters or tune the parameters from scratch (a value of <code>CLE AR</code> for the operation argument).</p></li></ul> <div align="center"> <div class="inftblnote"><br /> <table class="note" summary= "This is a layout table to format a note" title="This is a layout table to format a note" dir="ltr" border="1" width="80%" frame="hsi des" rules="groups" cellpadding="3" cellspacing="0"> <tbody> <tr> <td align="left"> <p><font face="arial, helvetica, sans-serif"><str ong>Note:</strong></font></p> This procedure is useful only when an attribute set is shared across multiple expression sets. In all o ther cases, the defaults assigned to the attribute set can be tuned for the expression set using it.</td></tr></tbody></table> <br /> </div>  </div> <p>See <a href="indexexpressions.htm#CJAHHDFF">"Creating an Index from Exact Parameters"</a> and <a href="#CJAJGJFE">"XPINDEX_PARAMETERS Procedure"</a> for more information.</p> <p>Related views: USER_EXPFIL_EXPRESSION_SETS, USER_EXPFIL_DEF_INDEX_PARAMETERS and USER_EXPFIL_INDEX_PARAMETERS.</p> <a id="sthref374" name="sthref374"></a> <p><strong><font face ="arial, helvetica, sans-serif" color="#330099">Examples</font></strong></p> <p>The following command synchronizes the expression set 's index parameters with the defaults associated with the corresponding attribute set:</p> <pre xml:space="preserve">BEGIN DBMS_EXP FIL.INDEX_PARAMETERS(expr_tab => 'Consumer', expr_col => 'Interest', attr_list => null, operation => 'DEFAULT'); END; / </pre> <p>The following command adds a stored attribute to the expression set's index parameters.</p> <pre xml:space="preserve">BEGIN DBMS_EXPFIL.INDEX_PARAMETERS(expr_ tab => 'Consumer', expr_col => 'Interest', attr_list => exf$attribute_list ( exf$attribute ( attr_name => 'CrashTestRating(Model, Year)', attr_oper => exf$indexoper('all'), attr_indexed => 'FALSE')), operation => 'ADD'); END; / </pre> <p>The fol lowing command clears the index parameters associated with the expression set:</p> <pre xml:space="preserve">BEGIN DBMS_EXPFIL.INDE X_PARAMETERS(expr_tab => 'Consumer', expr_col => 'Interest', at tr_list => null, operation => 'CLEAR'); END; / </pre> <p>A subsequent index creation will use the default index parameters assigned to the corresponding attribute set.</p></div>  <a id="CJABIGJJ" name=" CJABIGJJ"></a> <hr noshade="noshade" size="3" /> <div class="refsect1"><a id="sthref375" name ="sthref375"></a> <h2><font face="arial, helvetica, sans-serif" color="#330099"><a id="sthref376" name="sthref376"></a><a id="sthref 377" name="sthref377"></a><a id="sthref378" name="sthref378"></a>REVOKE_PRIVILEGE Procedure</font></h2> <p>This procedure revokes an expression privilege previously granted by the owner.</p> <a id="sthref379" name="sthref379"></a> <p><strong><font face="arial, helv etica, sans-serif" color="#330099">Format</font></strong></p> <pre xml:space="preserve">PROCEDURE REVOKE_PRIVILEGE ( ex pr_tab IN VARCHAR2, --- table with the expr column expr_col IN VARCHAR2, --- column storing the expressio n priv_type IN VARCHAR2, --- type of privilege to be revoked from_user IN VARCHAR2); --- user from which the priv is --- revoked </pre> <a id="sthref380" name="sthref380"></a> <p><s trong><font face="arial, helvetica, sans-serif" color="#330099">Arguments</font></strong></p> <dl> <dt><font size="-1" face="arial, h elvetica, sans-serif"><strong>expr_tab</strong></font></dt> <dd> <p>The table storing the expression set.</p></dd> <dt><font size="-1 " face="arial, helvetica, sans-serif"><strong>expr_col</strong></font></dt> <dd> <p>The column in the table that stores the expressio ns.</p></dd> <dt><font size="-1" face="arial, helvetica, sans-serif"><strong>priv_type</strong></font></dt> <dd> <p>Type of privilege to be revoked.</p></dd> <dt><font size="-1" face="arial, helvetica, sans-serif"><strong>from_user</strong></font></dt> <dd> <p>User from which the privilege is revoked.</p></dd></dl> <a id="sthref381" name="sthref381"></a> <p><strong><font face="arial, helvetica, sans-serif" color="#330099">Usage Notes</font></strong></p> <p>The REVOKE_PRIVILEGE procedure revokes an expression privilege previou sly granted by the owner.</p> <p>See <a href="#CJABDFBH">"GRANT_PRIVILEGE Procedure"</a> and <a href="expressionconcepts.htm#CHDBDBHG ">"Granting and Revoking Privileges"</a> for more information about granting and revoking privileges.</p> <p>Related views: USER_EXPF IL_EXPRESSION_SETS and USER_EXPFIL_PRIVILEGES.</p> <a id="sthref382" name="sthref382"></a> <p><strong><font face="arial, helvetica, sans-serif" color="#330099">Examples</font></strong></p> <p>The following command revokes the <code>INSERT EXPRESSION</code> privileg e on the <code>Interest</code> column of the <code>Consumer</code> table from user <code>SCOTT</code>:</p> <pre xml:space="preserve"> BEGIN DBMS_EXPFIL.REVOKE_PRIVILEGE (expr_tab => 'Consumer', expr_col => 'Interest', priv_type => 'INSERT EXPRESSION', from_user => 'SCOTT'); END;/ </pr e> </div>  <a id="CJADGDFB" name="CJADGDFB"></a> <hr noshade="noshade" size="3" /> <div class="refsect1"><! -- infolevel=all infotype=general --><a id="sthref383" name="sthref383"></a> <h2><font face="arial, helvetica, sans-serif" color="#3 30099"><a id="sthref384" name="sthref384"></a><a id="sthref385" name="sthref385"></a>UNASSIGN_ATTRIBUTE_SET Procedure</font></h2> <p> This procedure unassigns an attribute set from a column storing expressions.</p> <a id="sthref386" name="sthref386"></a> <p><strong> <font face="arial, helvetica, sans-serif" color="#330099">Format</font></strong></p> <pre xml:space="preserve">PROCEDURE UNASSIGN_ATT RIBUTE_SET ( expr_tab IN VARCHAR2, --- table with expr. column expr_col IN VARCHAR2); --- co lumn storing expr. set </pre> <a id="sthref387" name="sthref387"></a> <p><strong><font face="arial, helvetica, sans-serif" color="#3 30099">Arguments</font></strong></p> <dl> <dt><font size="-1" face="arial, helvetica, sans-serif"><strong>expr_tab</strong></font></d t> <dd> <p>The table storing the expression set.</p></dd> <dt><font size="-1" face="arial, helvetica, sans-serif"><strong>expr_col</s trong></font></dt> <dd> <p>The column in the table that stores the expressions.</p></dd></dl> <a id="sthref388" name="sthref388"></a> <p><strong><font face="arial, helvetica, sans-serif" color="#330099">Usage Notes</font></strong></p> <p>A column of an expression d atatype can be converted back to a <code>VARCHAR2</code> type by unassigning the attribute set. You can unassign an attribute set fro m a column storing expressions if an Expression Filter index is not defined on the column.</p> <p>See <a href="#CJAFAGID">"ASSIGN_ATT RIBUTE_SET Procedure"</a> for information about assigning attribute sets.</p> <p>Related views: USER_EXPFIL_EXPRESSION_SETS and USER_ EXPFIL_INDEXES.</p> <a id="sthref389" name="sthref389"></a> <p><strong><font face="arial, helvetica, sans-serif" color="#330099">Exa mples</font></strong></p> <p>The following command unassigns the attribute set previously assigned to the <code>Interest</code> colum n of the <code>Consumer</code> table. (See <a href="utilities.htm#CJABHDDD">Section 5.1</a>.)</p> <pre xml:space="preserve">BEGIN D BMS_EXPFIL.UNASSIGN_ATTRIBUTE_SET (expr_tab => 'Consumer', expr_col => 'Interest'); END; / </pre> </div>  <a id="CJAHCFCA" name="CJAHCFCA"></a> <hr noshade="noshade" size="3" /> <div class="refs ect1"><a id="sthref390" name="sthref390"></a> <h2><font face="arial, helvetica, sans-serif" c olor="#330099"><a id="sthref391" name="sthref391"></a><a id="sthref392" name="sthref392"></a>VALIDATE_EXPRESSIONS Procedure</font></h 2> <p>This procedure validates all the expressions in a set.</p> <a id="sthref393" name="sthref393"></a> <p><strong><font face="aria l, helvetica, sans-serif" color="#330099">Format</font></strong></p> <pre xml:space="preserve">PROCEDURE VALIDATE_EXPRESSIONS ( expr_tab IN VARCHAR2, --- expressions table expr_col IN VARCHAR2, --- column storing expression s exception_tab IN VARCHAR2 --- exception table default null); </pre> <a id="sthref394" name="s thref394"></a> <p><strong><font face="arial, helvetica, sans-serif" color="#330099">Arguments</font></strong></p> <dl> <dt><font size ="-1" face="arial, helvetica, sans-serif"><strong>expr_tab</strong></font></dt> <dd> <p>The table storing the expression set.</p></dd > <dt><font size="-1" face="arial, helvetica, sans-serif"><strong>expr_col</strong></font></dt> <dd> <p>The column in the table that stores the expressions.</p></dd> <dt><font size="-1" face="arial, helvetica, sans-serif"><strong>exception_tab</strong></font></dt> < dd> <p>Name of the exception table. This table is created using the BUILD_EXCEPTIONS_TABLE procedure.</p></dd></dl> <a id="sthref395" name="sthref395"></a> <p><strong><font face="arial, helvetica, sans-serif" color="#330099">Usage Notes</font></strong></p> <p>The e xpressions stored in a table may have references to schema objects like user-defined functions and tables. When these schema objects are dropped or modified, the expressions could become invalid and the subsequent evaluation (query with <code>EVALUATE</code> operato r) could fail.</p> <p>The VALIDATE_EXPRESSIONS procedure validates all the expressions in a set. By default, the expression validatio n utility fails on the first expression that is invalid. Optionally, the caller can pass an exception table to store references to al l the invalid expressions. In addition to validating expressions in the set, this procedure validates the parameters (stored and inde xed attributes) of the associated index and the approved list of user-defined functions. Any errors in the index parameters or the us er-defined function list are immediately reported to the caller.</p> <p>See <a href="expressionconcepts.htm#CHDBBCDH">"Evaluation Sem antics"</a> and <a href="#CJADCEED">"BUILD_EXCEPTIONS_TABLE Procedure"</a> for more information.</p> <p>Related views: USER_EXPFIL_EX PRESSION_SETS, USER_EXPFIL_ASET_FUNCTIONS, and USER_EXPFIL_PREDTAB_ATTRIBUTES.</p> <a id="sthref396" name="sthref396"></a> <p><stron g><font face="arial, helvetica, sans-serif" color="#330099">Examples</font></strong></p> <p>The following command validates the expre ssions stored in the <code>Interest</code> column of the <code>Consumer</code> table.</p> <pre xml:space="preserve">BEGIN DBMS_EXPF IL.VALIDATE_EXPRESSIONS (expr_tab => 'Consumer', expr_col => 'Interest'); END; / </pre> < /div>  <a id="CJAJGJFE" name="CJAJGJFE"></a> <hr noshade="noshade" size="3" /> <div class="refsect1"><a id="sthref397" name="sthref397"></a> <h2><font face="arial, helvetica, sans-serif" color="#33009 9"><a id="sthref398" name="sthref398"></a><a id="sthref399" name="sthref399"></a>XPINDEX_PARAMETERS Procedure</font></h2> <p>This pro cedure is used in conjunction with the INDEX_PARAMETERS procedure to fine-tune the XPath-specific index parameters for each expressio n set.</p> <a id="sthref400" name="sthref400"></a> <p><strong><font face="arial, helvetica, sans-serif" color="#330099">Format</font ></strong></p> <pre xml:space="preserve">PROCEDURE XPINDEX_PARAMETERS ( expr_tab IN VARCHAR2, --- table with exp r column expr_col IN VARCHAR2, --- column storing expressions xmlt_attr IN VARCHAR2, --- XML Type attrubue name xptag_list IN EXF$XPATH_TAGS, operation IN VARCHAR2 --- to ADD or DROP default 'ADD'); </pre> <a id="sthref401" name="sthref401"></a> <p><strong><font face="arial, helvetica, san s-serif" color="#330099">Arguments</font></strong></p> <dl> <dt><font size="-1" face="arial, helvetica, sans-serif"><strong>expr_tab< /strong></font></dt> <dd> <p>The table storing the expression set.</p></dd> <dt><font size="-1" face="arial, helvetica, sans-serif">< strong>expr_col</strong></font></dt> <dd> <p>The column in the table that stores the expressions.</p></dd> <dt><font size="-1" face=" arial, helvetica, sans-serif"><strong>xmlt_attr</strong></font></dt> <dd> <p>The name of the attribute with the <code>XMLType</code> datatype.</p></dd> <dt><font size="-1" face="arial, helvetica, sans-serif"><strong>xptag_list</strong></font></dt> <dd> <p>An instanc e of <code>EXF$XPATH_TAGS</code> type with a partial list of XML elements and attributes.</p></dd> <dt><font size="-1" face="arial, h elvetica, sans-serif"><strong>operation</strong></font></dt> <dd> <p>The operation to be performed on the list of index parameters. D efault value: <code>ADD</code>. Valid values: <code>ADD</code> or <code>DROP</code>.</p></dd></dl> <a id="sthref402" name="sthref402" ></a> <p><strong><font face="arial, helvetica, sans-serif" color="#330099">Usage Notes</font></strong></p> <p>When an attribute set is shared by multiple expression sets, the INDEX_PARAMETERS procedure can be used to tune the simple (non-XPath) index parameters for each expression set. The XPINDEX_PARAMETERS procedure is used in conjunction with the INDEX_PARAMETERS procedure to fine-tune the XP ath-specific index parameters for each expression set.</p> <p>See also <a href="#CJAEGGHA">"INDEX_PARAMETERS Procedure"</a> and <a hr ef="xpathpredicates.htm#CIHDBFHG">"Index Tuning for XPath Predicates"</a> for more information.</p> <p>Related views: USER_EXPFIL_ATT RIBUTES, USER_EXPFIL_DEF_INDEX_PARAMS, and USER_EXPFIL_INDEX_PARAMS.</p> <div align="center"> <div class="inftblinformal"><br /> <tab le class="informal" summary="This is a layout table to format a note" title="This is a layout table to format a note" dir="ltr" borde r="1" width="80%" frame="hsides" rules="groups" cellpadding="3" cellspacing="0"> <tbody> <tr align="left" valign="top"> <td align="le ft"> <p><font face="arial, helvetica, sans-serif"><strong>Note:</strong></font></p> The values assigned to the <code>tag_name</code> argument of <code>exf$xpath_tag</code> type are case-sensitive.</td></tr></tbody></table> <br /></div>  </div> <a id="sthref403" name="sthref403"></a> <p><strong><font face="arial, helvetica, sans-serif" color="#330099">Examples</font> </strong></p> <p>The following command synchronizes the expression set's index parameters (XPath and non-XPath) with the defaults ass ociated with the corresponding attribute set:</p> <pre xml:space="preserve">BEGIN DBMS_EXPFIL.INDEX_PARAMETERS(expr_tab => 'Con sumer', expr_col => 'Interest', attr_list => null, operation => 'DEFAULT'); END; / </pre> <p>The following command adds an XPath-specific index parameter to the expression set:</p> <pre xml:space="preserve">BEGIN DBMS_EXPFIL.XPINDEX_PARAMETERS(expr_tab => 'Consumer', expr_col => 'Interest', xmlt_attr => 'Details', xptag_list => exf$xpath_tags( exf$xpath_tag(tag_name = > 'GPS', tag_indexed => 'TRUE', tag_type => null)), operation => 'ADD'); END; / </pre> </div>  </div> <div class="footer"> <hr /> <table summary="" cellspacing="0" cellpadding="0" width="100%"> <tr> <td style="width: 33%;" alig n="left"> <table summary="layout table" cellspacing="0" cellpadding="0" align="left" width="90"> <tr> <td align="center" valign="top" ><a href="types.htm"><img src="../../dcommon/gifs/larrow.gif" alt="Go to previous page" border="0" /><br /> <font size="-2">Previous< /font></a></td> <td align="center" valign="top"><a href="views.htm"><img src="../../dcommon/gifs/rarrow.gif" alt="Go to next page" bo rder="0" /><br /> <font size="-2">Next</font></a></td></tr></table></td> <td style="width: 34%;" align="center"><img src="../../dcomm on/gifs/oracle.gif" alt="Oracle" /><br /> <a href="../../dcommon/html/cpyr.htm"><font size="-2">Copyright © 2003 Oracle Corporation</font></a><br /> <font size="-2">All Rights Reserved.</font></td> <td style="width: 33%;" align="right"> <tab le summary="" cellspacing="0" cellpadding="0" width="270"> <tr> <td align="center" valign="top"><a href="../../index.htm"><img src=". ./../dcommon/gifs/prodicon.gif" alt="Go to Documentation Home" border="0" /><br /> <font size="-2">Home</font></a></td> <td align="ce nter" valign="top"><a href="../../nav/portal_3.htm"><img src="../../dcommon/gifs/bookicon.gif" alt="Go to Book List" border="0" /><br /> <font size="-2">Book List</font></a></td> <td align="center" valign="top"><a href="toc.htm"><img src="../../dcommon/gifs/conticon .gif" alt="Go to Table of Contents" border="0" /><br /> <font size="-2">Contents</font></a></td> <td align="center" valign="top"><a h ref="index.htm"><img src="../../dcommon/gifs/indxicon.gif" alt="Go to Index" border="0" /><br /> <font size="-2">Index</font></a></td > <td align="center" valign="top"><a href="../../mix$101/b12039/toc.htm"><img src="../../dcommon/gifs/mix.gif" alt="Go to Master Inde x" border="0" /><br /> <font size="-2">Master Index</font></a></td> <td align="center" valign="top"><a href="../../dcommon/html/ feedback.htm"><img src="../../dcommon/gifs/feedback.gif" alt="Go to Feedback page" border="0" /><br /> <font size="-2">Feedback</font ></a></td></tr></table></td></tr></table></div> </body> </html>