You can create indexes on one or more relational columns, or on a functional expression. XML data, however, has its own, fine-grained structure, which is not necessarily reflected in the structure of the database tables used to store it. For this reason, effectively indexing XML data can be a bit different from indexing most database data.

For object-relational XMLType storage, XML objects such as elements and attributes correspond to object-relational columns and tables. Creating B-tree indexes on those columns and tables thus provides an excellent way to effectively index the corresponding XML objects. Here, the storage model directly reflects the fine-grained structure of the XML data, so there is no special problem for indexing structured XML data. See Indexing XMLType Data Stored Object-Relationally.

In object-relational XMLType storage, an XML document is broken up and stored object-relationally, but you can choose to store one or more of its XML fragments as embedded CLOB instances. A typical use case for this is mapping an XML-schema complexType or a complex element to CLOB storage, because you generally access the entire fragment as a unit.

But such an embedded CLOB fragment also acts as an opaque unit when it comes to indexing; its parts are not indexed individually.

Similarly, standard indexing is not helpful for binary XML storage. In both of these cases, indexing a database column using the standard sorts of index (B-tree, bitmap) is generally not helpful for accessing particular parts of an XML document.

XMLIndex provides a general, XML-specific index that indexes the internal structure of XML data. One of its main purposes is to overcome the indexing limitation presented by binary XML storage.

• An XMLIndex index with an unstructured component indexes the XML tags of your document and identifies document fragments based on XPath expressions that target them. It can also index scalar node values, to provide quick lookup based on individual values or ranges of values. It also records document hierarchy information for each node it indexes: relations parent–child, ancestor–descendant, and sibling. This index component is particularly useful for queries that extract XML fragments from documents that have little or variable structure.

• An XMLIndex index with a structured component indexes highly structured and predictable parts of XML data that is nevertheless for the most part unstructured. This index component is particularly useful for queries that project and use such islands of structured content.

Besides accessing XML nodes such as elements and attributes, it is sometimes important to provide fast access to particular passages within XML text nodes. To query such content within XML data, you can use XQuery Full Text (XQFT) or Oracle-specific full-text constructs.

In either case, you create an appropriate Oracle Text (full-text) index. In the case of XQFT, the index is an XML search index, which is designed specifically for use with XMLType data stored as binary XML.

Full-text indexing is particularly useful for document-centric applications, which often contain a mix of XML elements and text-node content. Full-text searching can often be made more powerful, more focused, by combining it with structural XML searching, that is, by restricting it to certain parts of an XML document, which are identified by using XPath expressions.

Which indexes are used when more than one might apply in a given case? Cost-based optimization determines the index or indexes to use, so that performance is maximized.

Oracle Text indexes apply only to text, which for XML data means text nodes. Whenever text nodes are targeted and a corresponding Oracle Text index is defined, it is used. If other indexes are also appropriate in a particular context, then they can be used as well. However, just because an index is defined and it might appear applicable in a given situation does not mean that it will be used. It will not be used if the cost-based optimizer deems that its use is not cost-effective.

In releases prior to Oracle Database 11g Release 2 (11.2), function-based indexes were sometimes appropriate for use with XMLType data when an XPath expression targeted a singleton node. Oracle recommends that you use the structured component of XMLIndex instead.

Doing so obviates the overhead associated with maintenance operations on function-based indexes, and it increases the number of situations in which the optimizer can correctly select the index. No changes to existing DML statements are required as a result of this.

It continues to be the case that, for object-relational storage of XMLType, defining an index for (deprecated) Oracle SQL function extractValue often leads, by XPath rewrite, to automatic creation of B-tree indexes on the underlying objects (instead of a function-based index on extractValue). The XPath target here must be a singleton element or attribute. A similar shortcut exists for XMLCast applied to XMLQuery.

B-tree indexes can be used advantageously with object-relational XMLType storage — they provide sharp focus by targeting the underlying objects directly. They are generally ineffective, however, in addressing the detailed structure (elements and attributes) of an XML document stored using binary XML. That is the special domain of XMLIndex.

XMLIndex is a domain index; it is designed specifically for the domain of XML data. It is a logical index. An XMLIndex index can be used for SQL/XML functions XMLQuery, XMLTable, XMLExists, and XMLCast.

XMLIndex presents the following advantages over other indexing methods:

• An XMLIndex index is effective in any part of a query;  it is not limited to use in a WHERE clause. This is not the case for any of the other kinds of indexes you might use with XML data.

• An XMLIndex index with an unstructured component can speed access to both SELECT list data and FROM list data, making it useful for XML fragment extraction, in particular. Function-based indexes, which are deprecated, cannot be used to extract document fragments.

• You can use an XMLIndex index with either XML schema-based or non-schema-based XMLType data stored as binary XML. B-tree indexing is appropriate only for XML schema-based data that is stored object-relationally.

• You can use an XMLIndex index for searches with XPath expressions that target collections, that is, nodes that occur multiple times within a document. This is not the case for function-based indexes.

• You need no prior knowledge of the XPath expressions that might be used in queries. The unstructured component of an XMLIndex index can be completely general. This is not the case for function-based indexes.

• If you have prior knowledge of the XPath expressions to be used in queries, then you can improve performance either by using a structured XMLIndex component that targets fixed, structured islands of data that are queried often.

• XMLIndex indexing — both index creation and index maintenance — can be carried out in parallel, using multiple database processes. This is not the case for function-based indexes, which are deprecated.

XMLIndex is used to index XML data that is unstructured or semi-structured, that is, data that generally has little or no fixed structure. It applies to XMLType data that is stored as binary XML.

Semi-structured XML data can sometimes nevertheless contain islands of predictable, structured data. An XMLIndex index can therefore have two components: a structured component, used to index such islands, and an unstructured component, used to index data that has little or variable structure.

A structured component can help with queries that project and use islands of structured content. A typical example is a free-form specification with fixed fields author, date, and title. An unstructured component can help with queries that extract XML fragments. Either component can be omitted from a given XMLIndex index.

Unlike a structured component, an unstructured component is general and relatively untargeted. It is appropriate for general indexing of document-centric XML data. A typical example is an XML web document or a book chapter.

You can create an XMLIndex index with both structured and unstructured components. A typical use case is supporting queries that extract an XML fragment from a document whenever some structured data is also present. The unstructured component is used for the fragment extraction. The structured component is used for a query predicate that checks for the structured data (for example, in the SQL WHERE clause).

Though you can restrict an unstructured component to apply only to certain XPath subsets, its path table indexes node content that can be of different scalar types, which can require you to create multiple secondary indexes on the VALUE column to deal with the different data types — see Secondary Indexes on Column VALUE. Using an unstructured component alone can also lead to inefficiencies involving multiple probes and self-joins of its path table, for queries that project structured islands.

On the other hand, a structured component is not suited for queries that involve little structure or queries that extract XML fragments. Use a structured component to index structured islands of data; use an unstructured component to index data that has little structure.

The last row indicates the applicability of XMLIndex for different XML data use cases. It shows that XMLIndex is appropriate for semi-structured XML data, however it is stored (last three columns). And an XMLIndex index with a structured component is useful for document-centric data that contains structured islands (fourth column).

Figure 6-1 XML Use Cases and XML Indexing

Description of "Figure 6-1 XML Use Cases and XML Indexing"

You create and use the structured component of an XMLIndex index for queries that project fixed, structured islands of XML content, even if the surrounding data is relatively unstructured.

A structured XMLIndex component organizes such islands in a relational format. In this it is similar to SQL/XML function XMLTable, and the syntax you use to define the structured component reflects this similarity. The relational tables used to store the indexing data are data-type aware, and each column can be of a different scalar data type.

You can thus think of the act of creating the structured component of an XMLIndex index as decomposing a structured portion of your XML data into relational format. This differs from the object-relational storage model of XMLType in these ways:

• A structured index component explicitly decomposes particular portions of your data, which you specify — portions that you commonly query. Object-relational XMLType storage involves automatic decomposition of an entire XMLType table or column.

• The structured component of an XMLIndex index applies to both XML schema-based and non-schema-based data. Object-relational XMLType storage applies only to data that is based on an XML schema.

• The decomposed data for a structured XMLIndex component is stored in addition to the XMLType data, as an index, rather than being the storage model for the XMLType data itself.

• For a structured XMLIndex component, the same data can be projected multiple times, as columns of different data type.

The index content tables used for the structured component of an XMLIndex index are part of the index, but because they are normal relational tables you can, in turn, index them using any standard relational indexes, including indexes that satisfy primary-key and foreign-key constraints. You can also index them using domain indexes, such as an Oracle Text CONTEXT index.

Another way to look at the structured component of an XMLIndex index sees that it acts as a generalized function-based index. A function-based index is similar to a structured XMLIndex component that has only one relational column.

If you find that for a particular application you are creating multiple function-based indexes, then consider using an XMLIndex index with a structured component instead. Create also B-tree indexes on the columns of the structured index component.

• Ignore the Index Content Tables; They Are Transparent
  Although the index content tables of an XMLIndex structured component are normal relational tables, they are also read-only: you cannot add or drop their columns or modify (insert, update, or delete) their rows.
• Data Type Considerations for XMLIndex Structured Component
  The relational tables that are used for an XMLIndex structured component use SQL data types. XQuery expressions that are used in queries use XML data types (XML Schema data types and XQuery data types).
• Exchange Partitioning and XMLIndex
  In exchange partitioning, you exchange a table with a partition of another table. The first table must have the same structure as the partition of the second table, with which it is to be exchanged. The two tables must also be similar with respect to indexing with an XMLIndex index.

Unlike a B-tree index, which you define for a specific database column that represents an individual XML element or attribute, or the XMLIndex structured component, which applies to specific, structured document parts, the unstructured component of an XMLIndex index is, by default, very general.

Unless you specify a more narrow focus by detailing specific XPath expressions to use or not to use in indexing, an unstructured XMLIndex component applies to all possible XPath expressions for your XML data.

The unstructured component of an XMLIndex index has three logical parts:

• A path index – This indexes the XML tags of a document and identifies its various document fragments.

• An order index – This indexes the hierarchical positions of the nodes in an XML document. It keeps track of parent–child, ancestor–descendant, and sibling relations.

• A value index – This indexes the values of an XML document. It provides lookup by either value equality or value range. A value index is used for values in query predicates (WHERE clause).

The unstructured component of an XMLIndex index uses a path table and a set of (local) secondary indexes on the path table, which implement the logical parts described above. Two secondary indexes are created automatically:

• A pikey index, which implements the logical indexes for both path and order.

• A real value index, which implements the logical value index.

You can modify these two indexes or create additional secondary indexes. The path table and its secondary indexes are all owned by the owner of the base table upon which the XMLIndex index is created.

The pikey index handles paths and order relationships together, which gives the best performance in most cases. If you find in some particular case that the value index is not picked up when think it should be, you can replace the pikey index with separate indexes for the paths and order relationships. Such (optional) indexes are called path id and order key indexes, respectively. For best results, contact Oracle Support if you find that the pikey index is not sufficient for your needs in some case.

The path table contains one row for each indexed node in the XML document. For each indexed node, the path table stores:

• The corresponding rowid of the table that stores the document.

• A locator, which provides fast access to the corresponding document fragment. For binary XML storage of XML schema-based data, it also stores data-type information.

• An order key, to record the hierarchical position of the node in the document. You can think of this as a Dewey decimal key like that used in library cataloging and Internet protocol SNMP. In such a system, the key 3.21.5 represents the node position of the fifth child of the twenty-first child of the third child of the document root node.

• An identifier that represents an XPath path to the node.

• The effective text value of the node.

Table 6-6 shows the main information^Foot 2 that is in the path table.

The pikey index uses path table columns PATHID, RID, and ORDER_KEY to represent the path and order indexes. An optional path id index uses columns PATHID and RID to represent the path index. A value index is an index on the VALUE column.

Example 6-6 explores the contents of the path table for two purchase-order documents.

<PurchaseOrder>
 <Reference>SBELL-2002100912333601PDT</Reference>
 <Actions>
  <Action>
   <User>SVOLLMAN</User>
  </Action>
 </Actions>
 . . .
</PurchaseOrder>

<PurchaseOrder>
 <Reference>ABEL-20021127121040897PST</Reference>
 <Actions>
  <Action>
   <User>ZLOTKEY</User>
  </Action>
  <Action>
   <User>KING</User>
  </Action>
 </Actions>
 . . .
</PurchaseOrder>

• Ignore the Path Table – It Is Transparent
  Though you can create secondary indexes on path-table columns, you can generally ignore the path table itself.
• Column VALUE of an XMLIndex Path Table
  A secondary index on column VALUE is used with XPath expressions in a WHERE clause that have predicates involving string matches. For example:
• Secondary Indexes on Column VALUE
  Even if you do not specify a secondary index for column VALUE when you create an XMLIndex index, a default secondary index is created on column VALUE. This default index has the default properties — in particular, it is an index for text (string-valued) data only.
• XPath Expressions That Are Not Indexed by an XMLIndex Unstructured Component
  A few types of XPath expressions are not indexed by XMLIndex.

Basic operations on an XMLIndex index include creating it, dropping it, altering it, and examining it. Examples are presented.

CREATE INDEX po_xmlindex_ix ON po_binxml (OBJECT_VALUE) INDEXTYPE IS XDB.XMLIndex;

SELECT INDEX_NAME FROM USER_INDEXES
  WHERE TABLE_NAME = 'PO_BINXML' AND ITYP_NAME = 'XMLINDEX';

INDEX_NAME
---------------
PO_XMLINDEX_IX
 
1 row selected.

ALTER INDEX po_xmlindex_ix RENAME TO new_name_ix;

DROP INDEX new_name_ix;

You can perform various operations on an XMLIndex index that has an unstructured component, including manipulating the path table and the secondary indexes of that component.

CREATE INDEX po_xmlindex_ix ON po_binxml (OBJECT_VALUE) INDEXTYPE IS XDB.XMLIndex
  PARAMETERS ('PATH TABLE my_path_table');

SELECT PATH_TABLE_NAME FROM USER_XML_INDEXES
  WHERE TABLE_NAME = 'PO_BINXML' AND INDEX_NAME = 'PO_XMLINDEX_IX';
 
PATH_TABLE_NAME
------------------------------
SYS67567_PO_XMLINDE_PATH_TABLE
 
1 row selected.

CREATE INDEX po_xmlindex_ix ON po_binxml (OBJECT_VALUE) INDEXTYPE IS XDB.XMLIndex
  PARAMETERS
    ('PATH TABLE po_path_table
      (PCTFREE 5 PCTUSED 90 INITRANS 5
       STORAGE (INITIAL 1k NEXT 2k MINEXTENTS 3 BUFFER_POOL KEEP)
       NOLOGGING ENABLE ROW MOVEMENT PARALLEL 3)
      PIKEY INDEX po_pikey_ix (LOGGING PCTFREE 1 INITRANS 3)
      VALUE INDEX po_value_ix (LOGGING PCTFREE 1 INITRANS 3)');

ALTER INDEX po_xmlindex_ix PARAMETERS('DROP PATH TABLE');

SELECT INDEX_NAME, COLUMN_NAME, COLUMN_POSITION FROM USER_IND_COLUMNS
  WHERE TABLE_NAME IN (SELECT PATH_TABLE_NAME FROM USER_XML_INDEXES
                         WHERE INDEX_NAME = 'PO_XMLINDEX_IX')
  ORDER BY INDEX_NAME, COLUMN_NAME;
 
INDEX_NAME                     COLUMN_NAME  COLUMN_POSITION
------------------------------ ------------ ---------------
SYS67563_PO_XMLINDE_PIKEY_IX   ORDER_KEY                  3
SYS67563_PO_XMLINDE_PIKEY_IX   PATHID                     2
SYS67563_PO_XMLINDE_PIKEY_IX   RID                        1
SYS67563_PO_XMLINDE_VALUE_IX   SYS_NC00006$               1
 
4 rows selected.

• Creating Additional Secondary Indexes on an XMLIndex Path Table
  You can add extra secondary indexes to an XMLIndex unstructured component.

An XMLIndex structured component indexes specific islands of structure in your XML data.

To include a structured component in an XMLIndex index, you use a structured_clause in the PARAMETERS clause when you create or modify the XMLIndex index — see structured_clause ::=.

A structured_clause specifies the structured islands that you want to index. You use the keyword GROUP to specify each structured island: an island thus corresponds syntactically to a structure group. If you specify no group explicitly, then the predefined group DEFAULT_GROUP is used. For ALTER INDEX, you precede the GROUP keyword with the modification operation keyword: ADD_GROUP specifies a new group (island); DROP_GROUP deletes a group.

Why have multiple groups within a single index, instead of simply using multiple XMLIndex indexes? The reason is that XMLIndex is a domain index, and you can create only one domain index of a given type on a given database column.

The syntax for defining a structure group, that is, indexing a structured island, is similar to the syntax for invoking SQL/XML function XMLTable: you use keywords XMLTable and COLUMNS to define relational columns, and you use multilevel chaining of XMLTable to handle collections. To simplify the creation of such an index, you can use PL/SQL function DBMS_XMLSTORAGE_MANAGE.getSIDXDefFromView to provide exactly the XMLTable expression needed for creating the index.

• Using Namespaces and Storage Clauses with an XMLIndex Structured Component
  When you create an XMLIndex index that has a structured component you can specify XML namespaces and storage options to use.
• Adding a Structured Component to an XMLIndex Index
  You can use ALTER INDEX to add a structured component to an existing XMLIndex index.
• Using Non-Blocking ALTER INDEX with an XMLIndex Structured Component
  You can prevent ALTER INDEX from blocking when you add a group or column for the structured component of an XMLIndex index, so that queries that use the index do not need to wait.
• Modifying the Data Type of a Structured XMLIndex Component
  If an error is raised because some of your data does not match the data type used for the corresponding column of the structured XMLIndex component, you can in some cases simply modify the index by passing keyword MODIFY_COLUMN_TYPE to ALTER INDEX.
• Dropping an XMLIndex Structured Component
  If an XMLIndex index has both an unstructured and a structured component, then you can use ALTER INDEX to drop the structured component. You do this by dropping all of the structure groups that compose the structured component.
• Indexing the Relational Tables of a Structured XMLIndex Component
  Because the tables used for the structured component of an XMLIndex index are normal relational tables, you can index them using any standard relational indexes.

To know whether a particular XMLIndex index has been used in resolving a query, you can examine an execution plan for the query.

It is at query compile time that Oracle Database determines whether or not a given XMLIndex index can be used, that is, whether the query can be rewritten into a query against the index.

For an unstructured XMLIndex component, if it cannot be determined at compile time that an XPath expression in the query is a subset of the paths you specified to be used for XMLIndex indexing, then the unstructured component of the  index  is not used.

For example, if the path /PurchaseOrder/LineItems//* is included for indexing, then a query with /PurchaseOrder/LineItems/LineItem/Description can use the index, but a query with //Description cannot. The latter also matches potential Description elements that are not children of /PurchaseOrder/LineItems, and it is not possible at compile time to know if such additional Description elements are present in the data.

You can examine the execution plan for a query to see whether a particular XMLIndex index has been used in resolving the query.

• If the unstructured component of the index is used, then its path table, order key, or path id is referenced in the execution plan. The execution plan does not directly indicate that a domain index was used; it does not refer to the XMLIndex index by name. See Example 6-27 and Example 6-29.

• If the structured component of the index is used, then one or more of its index content tables is called out in the execution plan. See Example 6-30 and Example 6-31.

Example 6-27 shows that the XMLIndex index created in Example 6-10 is used in a particular query. The reference to MY_PATH_TABLE in the execution plan here indicates that the XMLIndex index (created in Example 6-10) is used in this query. Similarly, reference to columns LOCATOR, ORDER_KEY, and PATHID indicates the same thing.

Given the name of a path table from an execution plan such as this, you can obtain the name of its XMLIndex index as shown in Example 6-28. (This is more or less opposite to the query in Example 6-11.)

XMLIndex can be used for XPath expressions in the SELECT list, the FROM list, and the WHERE clause of a query, and it is useful for SQL/XML functions XMLQuery, XMLTable, XMLExists, and XMLCast. Unlike function-based indexes, which are deprecated for XMLType, XMLIndex indexes can be used when you extract data from an XML fragment in a document.

Example 6-29 illustrates this.

The execution plan for the query in Example 6-29 shows, by referring to the path table, that XMLIndex is used. It also shows the use of Oracle internal SQL function sys_orderkey_depth — see Guidelines for Using XMLIndex with an Unstructured Component.

Example 6-30 shows an execution plan that indicates that the XMLIndex index created in Example 6-23 is picked up for a query that uses two WHERE clause predicates. It is the same query as in Example 6-46, and the same XML search index is in effect, as is also shown in the execution plan.

With only the unstructured XMLIndex component, the query would have involved a join of the path table to itself, because of the two different paths in the WHERE clause.

The presence in Example 6-30 of the path table name, path_tab, indicates that the unstructured component of the index is used. The presence of the index content table po_idx_tab indicates that the structured index component is used. The presence of the XML search index, po_ctx_idx, indicates that it too is used.

Example 6-31 shows an execution plan that indicates that the same XMLIndex index is also picked up for a query that uses multilevel XMLTable chaining. With only the unstructured XMLIndex component, this query too would involve a join of the path table to itself, because of the different paths in the two XMLTable function calls.

The execution plan shows direct access to the relational index content tables, po_idx_tab and po_index_lineitem. There is no access at all to the path table, path_tab.

SET AUTOTRACE ON EXPLAIN

SELECT XMLQuery('/PurchaseOrder/Requestor' PASSING OBJECT_VALUE RETURNING CONTENT) FROM po_binxml
  WHERE XMLExists('/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]' PASSING OBJECT_VALUE);
 
XMLQUERY('/PURCHASEORDER/REQUESTOR'PASSINGOBJECT_VALUERETURNINGCONTENT)
-----------------------------------------------------------------------
<Requestor>Sarah J. Bell</Requestor>
 
1 row selected.
 
 
Execution Plan
. . .
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                              |     1 |    24 |    28   (4)| 00:00:01 |
|   1 |  SORT GROUP BY                  |                              |     1 |  3524 |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID   | MY_PATH_TABLE                |     2 |  7048 |     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN             | SYS67616_PO_XMLINDE_PIKEY_IX |     1 |       |     2   (0)| 00:00:01 |
|   4 |  NESTED LOOPS                   |                              |     1 |    24 |    28   (4)| 00:00:01 |
|   5 |   VIEW                          | VW_SQ_1                      |     1 |    12 |    26   (0)| 00:00:01 |
|   6 |    HASH UNIQUE                  |                              |     1 |  5046 |            |          |
|   7 |     NESTED LOOPS                |                              |     1 |  5046 |    26   (0)| 00:00:01 |
|*  8 |      TABLE ACCESS BY INDEX ROWID| MY_PATH_TABLE                |     1 |  3524 |    24   (0)| 00:00:01 |
|*  9 |       INDEX RANGE SCAN          | SYS67616_PO_XMLINDE_VALUE_IX |    73 |       |     1   (0)| 00:00:01 |
|* 10 |      TABLE ACCESS BY INDEX ROWID| MY_PATH_TABLE                |     1 |  1522 |     2   (0)| 00:00:01 |
|* 11 |       INDEX RANGE SCAN          | SYS67616_PO_XMLINDE_PIKEY_IX |     1 |       |     1   (0)| 00:00:01 |
|  12 |   TABLE ACCESS BY USER ROWID    | PO_BINXML                    |     1 |    12 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(SYS_XMLI_LOC_ISNODE("SYS_P0"."LOCATOR")=1)
   3 - access("SYS_P0"."RID"=:B1 AND "SYS_P0"."PATHID"=HEXTORAW('76E2') )
   8 - filter("SYS_P4"."VALUE"='SBELL-2002100912333601PDT' AND "SYS_P4"."PATHID"=HEXTORAW('4F8C')  AND
              SYS_XMLI_LOC_ISNODE("SYS_P4"."LOCATOR")=1)
   9 - access(SUBSTRB("VALUE",1,1599)='SBELL-2002100912333601PDT')
  10 - filter(SYS_XMLI_LOC_ISNODE("SYS_P2"."LOCATOR")=1)
  11 - access("SYS_P4"."RID"="SYS_P2"."RID" AND "SYS_P2"."PATHID"=HEXTORAW('4E36')  AND
              "SYS_P2"."ORDER_KEY"<"SYS_P4"."ORDER_KEY")
       filter("SYS_P4"."ORDER_KEY"<SYS_ORDERKEY_MAXCHILD("SYS_P2"."ORDER_KEY") AND
              SYS_ORDERKEY_DEPTH("SYS_P2"."ORDER_KEY")+1=SYS_ORDERKEY_DEPTH("SYS_P4"."ORDER_KEY"))
. . .

SELECT INDEX_NAME FROM USER_XML_INDEXES WHERE PATH_TABLE_NAME = 'MY_PATH_TABLE';
 
INDEX_NAME
------------------------------
PO_XMLINDEX_IX
 
1 row selected.

SET AUTOTRACE ON EXPLAIN

 
SELECT li.description, li.itemno
  FROM po_binxml, XMLTable('/PurchaseOrder/LineItems/LineItem'
                           PASSING OBJECT_VALUE
                           COLUMNS "DESCRIPTION" VARCHAR(40) PATH 'Description',
                                   "ITEMNO"      INTEGER     PATH '@ItemNumber') li
  WHERE XMLExists('/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]'
                  PASSING OBJECT_VALUE);
 
DESCRIPTION                                  ITEMNO
---------------------------------------- ----------
A Night to Remember                               1
The Unbearable Lightness Of Being                 2
Sisters                                           3
 
3 rows selected.

Execution Plan

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                         | Rows  | Bytes |Cost (%CPU)| Time    |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                              |     1 |  1546 |   30   (4)|00:00:01 |
|*  1 |  FILTER                           |                              |       |       |           |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID     | MY_PATH_TABLE                |     1 |  3524 |    3   (0)|00:00:01 |
|*  3 |    INDEX RANGE SCAN               | SYS67616_PO_XMLINDE_PIKEY_IX |     1 |       |    2   (0)|00:00:01 |
|*  4 |  FILTER                           |                              |       |       |           |         |
|*  5 |   TABLE ACCESS BY INDEX ROWID     | MY_PATH_TABLE                |     1 |  3524 |    3   (0)|00:00:01 |
|*  6 |    INDEX RANGE SCAN               | SYS67616_PO_XMLINDE_PIKEY_IX |     1 |       |    2   (0)|00:00:01 |
|   7 |  NESTED LOOPS                     |                              |       |       |           |         |
|   8 |   NESTED LOOPS                    |                              |     1 |  1546 |   30   (4)|00:00:01 |
|   9 |    NESTED LOOPS                   |                              |     1 |    24 |   28   (4)|00:00:01 |
|  10 |     VIEW                          | VW_SQ_1                      |     1 |    12 |   26   (0)|00:00:01 |
|  11 |      HASH UNIQUE                  |                              |     1 |  5046 |           |         |
|  12 |       NESTED LOOPS                |                              |     1 |  5046 |   26   (0)|00:00:01 |
|* 13 |        TABLE ACCESS BY INDEX ROWID| MY_PATH_TABLE                |     1 |  3524 |   24   (0)|00:00:01 |
|* 14 |         INDEX RANGE SCAN          | SYS67616_PO_XMLINDE_VALUE_IX |    73 |       |    1   (0)|00:00:01 |
|* 15 |        TABLE ACCESS BY INDEX ROWID| MY_PATH_TABLE                |     1 |  1522 |    2   (0)|00:00:01 |
|* 16 |         INDEX RANGE SCAN          | SYS67616_PO_XMLINDE_PIKEY_IX |     1 |       |    1   (0)|00:00:01 |
|  17 |     TABLE ACCESS BY USER ROWID    | PO_BINXML                    |     1 |    12 |    1   (0)|00:00:01 |
|* 18 |    INDEX RANGE SCAN               | SYS67616_PO_XMLINDE_PIKEY_IX |     1 |       |    1   (0)|00:00:01 |
|* 19 |   TABLE ACCESS BY INDEX ROWID     | MY_PATH_TABLE                |     1 |  1522 |    2   (0)|00:00:01 |
----------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(:B1<SYS_ORDERKEY_MAXCHILD(:B2))
   2 - filter(SYS_XMLI_LOC_ISNODE("SYS_P2"."LOCATOR")=1)
   3 - access("SYS_P2"."RID"=:B1 AND "SYS_P2"."PATHID"=HEXTORAW('28EC')  AND "SYS_P2"."ORDER_KEY">:B2 AND
              "SYS_P2"."ORDER_KEY"<SYS_ORDERKEY_MAXCHILD(:B3))
       filter(SYS_ORDERKEY_DEPTH("SYS_P2"."ORDER_KEY")=SYS_ORDERKEY_DEPTH(:B1)+1)
   4 - filter(:B1<SYS_ORDERKEY_MAXCHILD(:B2))
   5 - filter(SYS_XMLI_LOC_ISNODE("SYS_P5"."LOCATOR")=1)
   6 - access("SYS_P5"."RID"=:B1 AND "SYS_P5"."PATHID"=HEXTORAW('60E0')  AND "SYS_P5"."ORDER_KEY">:B2 AND
              "SYS_P5"."ORDER_KEY"<SYS_ORDERKEY_MAXCHILD(:B3))
       filter(SYS_ORDERKEY_DEPTH("SYS_P5"."ORDER_KEY")=SYS_ORDERKEY_DEPTH(:B1)+1)
  13 - filter("SYS_P10"."VALUE"='SBELL-2002100912333601PDT' AND "SYS_P10"."PATHID"=HEXTORAW('4F8C')  AND
              SYS_XMLI_LOC_ISNODE("SYS_P10"."LOCATOR")=1)
  14 - access(SUBSTRB("VALUE",1,1599)='SBELL-2002100912333601PDT')
  15 - filter(SYS_XMLI_LOC_ISNODE("SYS_P8"."LOCATOR")=1)
  16 - access("SYS_P10"."RID"="SYS_P8"."RID" AND "SYS_P8"."PATHID"=HEXTORAW('4E36')  AND
              "SYS_P8"."ORDER_KEY"<"SYS_P10"."ORDER_KEY")
       filter("SYS_P10"."ORDER_KEY"<SYS_ORDERKEY_MAXCHILD("SYS_P8"."ORDER_KEY") AND
              SYS_ORDERKEY_DEPTH("SYS_P8"."ORDER_KEY")+1=SYS_ORDERKEY_DEPTH("SYS_P10"."ORDER_KEY"))
  18 - access("PO_BINXML".ROWID="SYS_ALIAS_4"."RID" AND "SYS_ALIAS_4"."PATHID"=HEXTORAW('3748') )
  19 - filter(SYS_XMLI_LOC_ISNODE("SYS_ALIAS_4"."LOCATOR")=1)
 
Note
-----
   - dynamic sampling used for this statement (level=2)

EXPLAIN PLAN FOR
  SELECT XMLQuery('/PurchaseOrder/LineItems/LineItem'
                  PASSING OBJECT_VALUE RETURNING CONTENT)
    FROM po_binxml
    WHERE XMLExists('/PurchaseOrder/LineItems/LineItem
                     [Description contains text "Picnic"]'
                    PASSING OBJECT_VALUE)
      AND XMLExists('/PurchaseOrder[User="SBELL"]' PASSING OBJECT_VALUE);
 
Explained.
 

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                         |Rows|Bytes| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                              |   1| 2051|     9   (0)| 00:00:01 |
|   1 |  SORT GROUP BY                       |                              |   1| 3524|            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| PATH_TAB                     |   2| 7048|     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | SYS86751_PO_XMLINDE_PIKEY_IX |   1|     |     2   (0)| 00:00:01 |
|   4 |  NESTED LOOPS SEMI                   |                              |   1| 2051|     6   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID        | PO_BINXML                    |   1| 2024|     4   (0)| 00:00:01 |
|*  6 |    DOMAIN INDEX                      | PO_CTX_IDX                   |    |     |     4   (0)| 00:00:01 |
|*  7 |   TABLE ACCESS BY INDEX ROWID BATCHED| PO_IDX_TAB                   |  13|  351|     2   (0)| 00:00:01 |
|*  8 |    INDEX RANGE SCAN                  | SYS86751_86755_OID_IDX       |   1|     |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(SYS_XMLI_LOC_ISNODE("SYS_P1"."LOCATOR")=1)
   3 - access("SYS_P1"."RID"=:B1 AND "SYS_P1"."PATHID"=HEXTORAW('3748') )
   6 - access("CTXSYS"."CONTAINS"(SYS_MAKEXML(0,"XMLDATA"),'<query><textquery grammar="CONTEXT"
              lang="english">{Picnic} INPATH
              (/PurchaseOrder/LineItems/LineItem/Description)</textquery><xquery><offset>0</
              offset></xquery></query>')>0)
   7 - filter("SYS_SXI_0"."USERNAME"='SBELL')
   8 - access("PO_BINXML"."SYS_NC_OID$"="SYS_SXI_0"."OID")
 
Note
-----
   - dynamic sampling used for this statement (level=2)
 
30 rows selected.

EXPLAIN PLAN FOR
  SELECT po.reference, li.*
    FROM po_binxml p,
         XMLTable('/PurchaseOrder' PASSING p.OBJECT_VALUE
                  COLUMNS reference   VARCHAR2(30)  PATH 'Reference',
                          lineitem    XMLType       PATH 'LineItems/LineItem') po,
         XMLTable('/LineItem' PASSING po.lineitem
                  COLUMNS itemno      BINARY_DOUBLE PATH '@ItemNumber',
                          description VARCHAR2(256) PATH 'Description',
                          partno      VARCHAR2(14)  PATH 'Part/@Id',
                          quantity    BINARY_DOUBLE PATH 'Part/@Quantity',
                          unitprice   BINARY_DOUBLE PATH 'Part/@UnitPrice') li
    WHERE po.reference = 'SBELL-20021009123335280PDT';
 

-------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                        |    17 | 20366 |     8   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |                        |       |       |            |          |
|   2 |   NESTED LOOPS               |                        |    17 | 20366 |     8   (0)| 00:00:01 |
|   3 |    NESTED LOOPS              |                        |     1 |   539 |     3   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL        | PO_IDX_TAB             |     1 |   529 |     3   (0)| 00:00:01 |
|*  5 |     INDEX UNIQUE SCAN        | SYS_C007442            |     1 |    10 |     0   (0)| 00:00:01 |
|*  6 |    INDEX RANGE SCAN          | SYS86751_86759_PKY_IDX |    17 |       |     1   (0)| 00:00:01 |
|   7 |   TABLE ACCESS BY INDEX ROWID| PO_INDEX_LINEITEM      |    17 | 11203 |     5   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - filter("SYS_SXI_2"."REFERENCE"='SBELL-20021009123335280PDT')
   5 - access("P"."SYS_NC_OID$"="SYS_SXI_2"."OID")
   6 - access("SYS_SXI_2"."KEY"="SYS_SXI_3"."PKEY")
 
Note
-----
   - dynamic sampling used for this statement
 
25 rows selected.

You can turn off the use of XMLIndex by using optimizer hint: /*+ NO_XML_QUERY_REWRITE */ or optimizer hint /*+ NO_XMLINDEX_REWRITE */.

SELECT /*+ NO_XMLINDEX_REWRITE */ 
  count(*) FROM po_binxml WHERE XMLExists('$p/*' PASSING OBJECT_VALUE AS "p");

SELECT /*+ NO_XML_QUERY_REWRITE */
  count(*) FROM po_binxml WHERE XMLExists('$p/*' PASSING OBJECT_VALUE AS "p");

If you know which XPath expressions you are most likely to query then you can narrow the focus of XMLIndex indexing and thus improve performance.

ALTER INDEX po_xmlindex_ix REBUILD
  PARAMETERS ('PATHS (EXCLUDE ADD (/PurchaseOrder/Reference))');

• Examples of XMLIndex Path Subsetting
  Some examples are presented of defining XMLIndex indexes on subsets of XPath expressions.
• XMLIndex Path-Subsetting Rules
  Rules that apply to XMLIndex path subsetting are described.

There are several guidelines that can help you use XMLIndex with an unstructured component.

These guidelines are applicable only when the two alternatives discussed return the same result set.

• Avoid prefixing // with ancestor elements. For example, use //c, not /a/b//c, provided these return the same result set.

• Avoid prefixing /* with ancestor elements. For example, use /*/*/*, not /a/*/*, provided these return the same result set.

• In a WHERE clause, use XMLExists rather than XMLCast of XMLQuery. This can allow optimization that, in effect, invokes a subquery against the path-table VALUE column. For example, use this:

  SELECT count(*) FROM purchaseorder p 
    WHERE 
      XMLExists('$p/PurchaseOrder/LineItems/LineItem/Part[@Id="715515011020"]'
                    PASSING OBJECT_VALUE AS "p");
  

  Do not use this:

  SELECT count(*) FROM purchaseorder p
    WHERE XMLCast(XMLQuery('$p/PurchaseOrder/LineItems/LineItem/Part/@Id'
                           PASSING OBJECT_VALUE AS "p" RETURNING CONTENT)
                  AS VARCHAR2(14))
          = "715515011020";
  

• When possible, use count(*), not count(XMLCast(XMLQuery(...)), in a SELECT clause. For example, if you know that a LineItem element in a purchase-order document has only one Description child, use this:

  SELECT count(*) FROM po_binxml, XMLTable('//LineItem' PASSING OBJECT_VALUE);
  

  Do not use this:

  SELECT count(li.value)
   FROM po_binxml p, XMLTable('//LineItem' PASSING p.OBJECT_VALUE
                              COLUMNS value VARCHAR2(30) PATH 'Description') li;
  

• Reduce the number of XPath expressions used in a query FROM list as much as possible. For example, use this:

  SELECT li.description
    FROM po_binxml p,
         XMLTable('PurchaseOrder/LineItems/LineItem' PASSING p.OBJECT_VALUE
                  COLUMNS description VARCHAR2(256) PATH 'Description') li;
  

  Do not use this:

  SELECT li.description
    FROM po_binxml p,
         XMLTable('PurchaseOrder/LineItems' PASSING p.OBJECT_VALUE) ls,
         XMLTable('LineItems/LineItem'      PASSING ls.OBJECT_VALUE
                  COLUMNS description VARCHAR2(256) PATH 'Description') li;
  

• If you use an XPath expression in a query to drill down inside a virtual table (created, for example, using SQL/XML function XMLTable), then create a secondary index on the order key of the path table using Oracle SQL function sys_orderkey_depth. Here is an example of such a query; the selection navigates to element Description inside virtual line-item table li.

  SELECT li.description
    FROM po_binxml p,
         XMLTable('PurchaseOrder/LineItems/LineItem' PASSING p.OBJECT_VALUE
                  COLUMNS description VARCHAR2(256) PATH 'Description') li;
  

  Such queries are evaluated using function sys_orderkey_depth, which returns the depth of the order-key value. Because the order index uses two columns, the index needed is a composite index over columns ORDER_KEY and RID, as well as over function sys_orderkey_depth applied to the ORDER_KEY value. For example:

  CREATE INDEX depth_ix ON my_path_table
    (RID, sys_orderkey_depth(ORDER_KEY), ORDER_KEY);

  See Also:

  Example 6-29 for an example that shows the use of sys_orderkey_depth

There are several guidelines that can help you use XMLIndex with a structured component.

• Use XMLIndex with a structured component to project and index XML data as relational columns. Do not use function-based indexes; they are deprecated for use with XML. See Function-Based Indexes Are Deprecated for XMLType.

• Ensure data type correspondence between a query and an XMLIndex index that has a structured component. See Data Type Considerations for XMLIndex Structured Component.

• If you create a relational view over XMLType data (for example, using SQL function XMLTable), then consider also creating an XMLIndex index with a structured component that targets the same relational columns. See Relational Views over XML Data.

• Instead of using a single XQuery expression for both fragment extraction and value filtering (search), use SQL/XML function XMLQuery in the SELECT clause to extract fragments and XMLExists in the WHERE clause to filter values.

  This lets Oracle XML DB evaluate fragment extraction functionally or by using streaming evaluation. For value filtering, this lets Oracle XML DB pick up an XMLIndex index that has a relevant structured component.

• To order query results, use a SQL ORDER BY clause, together with SQL/XML function XMLTable. Avoid using the XQuery order by clause. This is particularly pertinent if you use an XMLIndex index with a structured component.

If you partition an XMLType table, or a table with an XMLType column, using range, list, or hash partitioning, you can also create an XMLIndex index on the table. You can optionally ensure that index creation and maintenance are carried out in parallel.

{ NOPARALLEL | PARALLEL [ integer ] }

CREATE INDEX po_xmlindex_ix ON sale_info (sale_po_clob) INDEXTYPE IS XDB.XMLIndex 
  LOCAL PARALLEL 10;

CREATE INDEX po_xmlindex_ix ON sale_info (sale_po_clob) INDEXTYPE IS XDB.XMLIndex 
  LOCAL NOPARALLEL PARAMETERS ('PATH TABLE po_path_table (PARALLEL 10)
                                PIKEY INDEX po_pikey_ix
                                VALUE INDEX po_value_ix (PARALLEL 5)');

You can defer the cost of maintaining an XMLIndex index that has only an unstructured component, performing maintenance only at commit time or when database load is reduced. This can improve DML performance, and it can enable bulk loading of unsynchronized index rows when an index is synchronized.

This feature applies to an XMLIndex index that has only an unstructured component. If you specify asynchronous maintenance for an XMLIndex index that has a structured component (even if it also has an unstructured component), then an error is raised.

By default, XMLIndex indexing is updated (maintained) at each DML operation, so that it remains in sync with the base table. In some situations, you might not require this, and using possibly stale indexes might be acceptable. In that use case, you can decide to defer the cost of index maintenance, performing at commit time only or at some time when database load is reduced. This can improve DML performance. It can also improve index maintenance performance by enabling bulk loading of unsynchronized index rows when an index is synchronized.

Using a stale index has no effect, other than performance, on DML operations. It can have an effect on query results, however: If the index is not up-to-date at query time, then the query results might not be up-to-date either. Even if only one column of a base table is of data type XMLType, all queries on that table reflect the database data as of the last synchronization of the XMLIndex index on the XMLType column.

You can specify index maintenance deferment using the parameters clause of a CREATE INDEX or ALTER INDEX statement.

Be aware that even if you defer synchronization for an XMLIndex index, the following database operations automatically synchronize the index:

• Any DDL operation on the index – ALTER INDEX or creation of secondary indexes

• Any DDL operation on the base table – ALTER TABLE or creation of another index

Table 6-7 lists the synchronization options and the ASYNC clause syntax you use to specify them. The ASYNC clause is used in the PARAMETERS clause of a CREATE INDEX or ALTER INDEX statement for XMLIndex.

Optional ASYNC syntax parameter STALE is intended for possible future use; you need never specify it explicitly. It has value FALSE whenever ALWAYS is used; otherwise it has value TRUE. Specifying an explicit STALE value that contradicts this rule raises an error.

Example 6-38 creates an XMLIndex index that is synchronized every Monday at 3:00 pm, starting tomorrow.

Example 6-39 manually synchronizes the index created in Example 6-38.

When XMLIndex index synchronization is deferred, all DML changes (inserts, updates, and deletions) made to the base table since the last index synchronization are recorded in a pending table, one row per DML operation. The name of this table is the value of column PEND_TABLE_NAME of static public views USER_XML_INDEXES, ALL_XML_INDEXES, and DBA_XML_INDEXES.

You can examine this table to determine when synchronization might be appropriate for a given XMLIndex index. The more rows there are in the pending table, the more the index is likely to be in need of synchronization.

If the pending table is large, then setting parameter REINDEX to TRUE when calling syncIndex, as in Example 6-39, can improve performance. When REINDEX is TRUE, all of the secondary indexes are dropped and then re-created after the pending table data is bulk-loaded.

CREATE INDEX po_xmlindex_ix ON po_binxml (OBJECT_VALUE) INDEXTYPE IS XDB.XMLIndex
  PARAMETERS ('ASYNC (SYNC EVERY "FREQ=HOURLY; INTERVAL = 1")');

EXEC DBMS_XMLINDEX.syncIndex('OE', 'PO_XMLINDEX_IX', REINDEX => TRUE);

• Syncing an XMLIndex Index in Case of Error ORA-08181
  If a query raises error ORA-08181, check whether the base XMLType table of the query has an XMLIndex index with an unstructured component. If so, then manually synchronize the XMLIndex index using DBMS_XMLINDEX.syncIndex.

The Oracle Database cost-based optimizer determines how to most cost-effectively evaluate a given query, including which indexes, if any, to use. For it to be able to do this accurately, you must collect statistics on various database objects.

CALL DBMS_STATS.gather_table_stats(USER, 'PO_BINXML', ESTIMATE_PERCENT => NULL);

Information about the standard database indexes is available in static public views USER_INDEXES, ALL_INDEXES, and DBA_INDEXES. Similar information about XMLIndex indexes is available in static public views USER_XML_INDEXES, ALL_XML_INDEXES, and DBA_XML_INDEXES.

Creation or modification of an XMLIndex index often involves the use of a PARAMETERS clause with SQL statement CREATE INDEX or ALTER INDEX. You can use it to specify index characteristics in detail.

• Using a Registered PARAMETERS Clause for XMLIndex
  The string value used for the PARAMETERS clause of a CREATE INDEX or ALTER INDEX statement has a 1000-character limit. To get around this limitation, you can use PL/SQL procedures registerParameter and modifyParameter in package DBMS_XMLINDEX.
• PARAMETERS Clause Syntax for CREATE INDEX and ALTER INDEX
  The syntax for the PARAMETERS clause for CREATE INDEX and ALTER INDEX is defined.
• Usage of XMLIndex_parameters_clause
  When you create an XMLIndex index, if there is no XMLIndex_parameters_clause, then the new index has only an unstructured component. If there is an XMLIndex_parameters_clause, but the PARAMETERS argument is empty (''), then the result is the same: an index with only an unstructured component.
• Usage of XMLIndex_parameters
  Certain considerations apply to using XMLIndex_parameters.
• Usage of PATHS Clause
  Certain considerations apply to using the PATHS clause.
• Usage of create_index_paths_clause and alter_index_paths_clause
  Certain considerations apply to using create_index_paths_clause and alter_index_paths_clause.
• Usage of pikey_clause, path_id_clause, and order_key_clause
  Syntactically, each of the clauses pikey_clause, path_id_clause, and order_key_clause is optional. A pikey index is created even if you do not specify a pikey_clause. To create a path id index or an order-key index, you must specify a path_id_clause or an order_key_clause, respectively.
• Usage of value_clause
  Certain considerations apply to using value_clause.
• Usage of async_clause
  Certain considerations apply to using the ASYNC clause.
• Usage of groups_clause and alter_index_group_clause
  Clause groups_clause is used only with CREATE INDEX (or following ADD GROUP in clause alter_index_group_clause). Clause alter_index_group_clause is used only with ALTER INDEX.
• Usage of XMLIndex_xmltable_clause
  Certain considerations apply to using XMLIndex_xmltable_clause.
• Usage of column_clause
  Certain considerations apply to using column_clause.

An XQuery Full Text query can use an XML search index to improve performance.

BEGIN
  CTX_DDL.create_section_group('mysecgroup', 'PATH_SECTION_GROUP');
  CTX_DDL.set_sec_grp_attr('mysecgroup', 'XML_ENABLE', 'T');

  CTX_DDL.create_preference('mypref', 'BASIC_STORAGE');
  CTX_DDL.set_attribute('mypref',
                        'D_TABLE_CLAUSE',
                        'TABLESPACE my_ts
                         LOB(DOC) STORE AS SECUREFILE 
                         (TABLESPACE my_ts COMPRESS MEDIUM CACHE)');
  CTX_DDL.set_attribute('mypref',
                        'I_TABLE_CLAUSE',
                        'TABLESPACE my_ts
                         LOB(TOKEN_INFO) STORE AS SECUREFILE
                         (TABLESPACE my_ts NOCOMPRESS CACHE)');
END;
/

CREATE INDEX po_ctx_idx ON po_binxml(OBJECT_VALUE)
  INDEXTYPE IS CTXSYS.CONTEXT
  PARAMETERS('storage mypref section group mysecgroup');

SELECT XMLQuery('for $i in /PurchaseOrder/LineItems/LineItem/Description
                   where $i[. contains text "Big" ftand "Street"]
               return <Title>{$i}</Title>'
               PASSING OBJECT_VALUE RETURNING CONTENT)
  FROM po_binxml
  WHERE XMLExists('/PurchaseOrder/LineItems/LineItem/Description
                   [. contains text "Big" ftand "Street"]'
                  PASSING OBJECT_VALUE);

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |  2014 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| PO_BINXML  |     1 |  2014 |     4   (0)| 00:00:01 |
|*  2 |   DOMAIN INDEX              | PO_CTX_IDX |       |       |     4   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("CTXSYS"."CONTAINS"(SYS_MAKEXML(0,"XMLDATA"),'<query><textquery
              grammar="CONTEXT" lang="english"> ( ( {Big} ) and ( {Street} ) )  INPATH
              (/PurchaseOrder/LineItems/LineItem/Description)</textquery></query>')>0)
 
Note
-----
   - dynamic sampling used for this statement (level=2)
   - Unoptimized XML construct detected (enable XMLOptimizationCheck for more information)
 
21 rows selected.

You can modify your query to ensure that certain conditions are satisfied, so its evaluation picks up an XML search index.

Oracle recommends in general that you use non XML Schema-based XMLType data when you use XQuery Full Text and an XML search index. But you can in some circumstances use XML Schema-based XMLType data that is stored as binary XML. Oracle XQuery pragma ora:no_schema can be useful in this context.

By default, when an XML search index is used to evaluate XML Schema-based data, compile-time error ORA-18177 is raised. This is because the full-text indexing itself makes no use of the associated XML schema: it is not type-aware. It treats all of the text that it applies to as untyped. This error is raised even if you type-cast data appropriately and thus do not depend on the XML schema to cast types implicitly. Example 6-44 illustrates this.

The error raised draws this to your attention, in case you might be expecting a full-text condition in your query to depend on XML Schema types and typed operations.

In order to use a condition that depends on types you must explicitly cast the relevant XQuery expressions to the appropriate types. Do not expect Oracle XML DB to use the XML schema to perform implicit type casting. Failure to type-cast appropriately can lead to results that you might not expect.

Example 6-45 shows a query of XML Schema-based data that uses explicit type-casting to ensure that the proper condition is evaluated.

However, most uses of XQuery Full Text expressions, even with XML Schema-based data, do not involve data that is typed. Just remember that if you do use a condition that makes use of typed data then you must cast to the proper type.

In sum, if you are sure that your query does not involve typed data, or if you judge that it is all right to treat particular typed data as if it were untyped, or if you explicitly type-cast any data that needs to be typed, then you can use Oracle XQuery pragma ora:no_schema in your query to inhibit raising the error and allow evaluation of the query using an XML search index.

SELECT XMLQuery('/PurchaseOrder/LineItems/LineItem'
                PASSING OBJECT_VALUE RETURNING CONTENT)
  FROM oe.purchaseorder
  WHERE XMLExists('/PurchaseOrder
                   [LineItems/LineItem/@ItemNumber > xs:integer("20")
                    and Actions/Action/User contains text "KPARTNER"]'
                  PASSING OBJECT_VALUE);
  FROM oe.purchaseorder
          *
ERROR at line 3:
ORA-18177: XQuery full text expression '/PurchaseOrder
[LineItems/LineItem/@ItemNumber > xs:integer("20")
and Actions/Action/User contains text "KPARTNER"]'
cannot be evaluated using XML text index

SELECT XMLQuery('/PurchaseOrder/LineItems/LineItem'
                PASSING OBJECT_VALUE RETURNING CONTENT)
  FROM oe.purchaseorder
  WHERE XMLExists('(# ora:no_schema #)
                   {/PurchaseOrder
                    [LineItems/LineItem/@ItemNumber > xs:integer("20")
                     and Actions/Action/User contains text "KPARTNER"]}'
                  PASSING OBJECT_VALUE);

You can use XQuery pragma ora:use_xmltext_idx to force the use of an XML search index.

A given query involving XML data can be evaluated in various ways, depending on the existence of different indexes and other factors. Sometimes the default evaluation method is not the most performant and it would be more efficient to force the use of an existing XML search index. You can use XQuery pragma ora:use_xmltext_idx to do this. (An XML search index applies only to XMLType data stored as binary XML.)

For example, a WHERE clause might include two XMLExists expressions, only one of which involves an XQuery full-text condition, and you might have an XMLIndex index that applies to the XMLExists expression that has no full-text condition. With such a query it is typically more efficient to use an XML search index to evaluate the entire WHERE clause.

Even in some cases where there is no full-text condition in the query, the use of an XML search index can provide the most efficient query evaluation.

The query in Example 6-46 illustrates the use of pragma ora:use_xmltext_idx. Only the first of the XMLExists clauses uses a full-text condition. Because of the pragma, the full-text index (po_ctx_idx, created in Example 6-41) is used for both XMLExists clauses.

SELECT XMLQuery('/PurchaseOrder/LineItems/LineItem'
                PASSING OBJECT_VALUE RETURNING CONTENT)
  FROM po_binxml
  WHERE XMLExists('/PurchaseOrder/LineItems/LineItem
                   [Description contains text "Picnic"]' PASSING OBJECT_VALUE)
    AND XMLExists('(# ora:use_xmltext_idx #) {/PurchaseOrder[User="SBELL"]}'
                  PASSING OBJECT_VALUE);

If you have legacy queries for XMLType data stored as binary XML that use SQL function CONTAINS or deprecated XPath function ora:contains and an Oracle Text index that is not XML-enabled, then consider using XQuery Full Text constructs instead.

CONTAINS(t.x, 'HASPATH (/P/LIs/LI/DescriptionFoot 3)') > 0

XMLExists('(# ora:use_xmltext_idx #) 
           {$d/P/LIs/LI/DescriptionFootref 3}'
          PASSING t.x AS "d")

XMLExists('(# ora:use_xmltext_idx #)
           {(# ora:no_schema #) 
            {$d/P/LIs/LI/DescriptionFootref 3}}'
          PASSING t.x AS "d")

CONTAINS(t.x, 'Big INPATH
               (/P/LIs/LI/Description)') > 0

XMLExists('$d/P/LIs/LI/Description
           [. contains text "Big"]'
          PASSING t.x AS "d")

XMLExists('(# ora:no_schema #)
           {$d/P/LIs/LI/Description
           [. contains text "Big"]}'
          PASSING t.x AS "d")

XMLExists('$d/P/LIs/LI/Description
           [ora:contains(., "Big AND Street") > 0]'
          PASSING t.x AS "d")

XMLExists('$d/P/LIs/LI/Description
           [. contains text "Big" ftand "Street"]'
          PASSING t.x AS "d")

CONTAINS(t.x, '(Big) AND (Street) INPATH
               (/P/LIs/LI/Description)') > 0

XMLExists('$d/P/LIs/LI/Description
           [. contains text "Big" ftand "Street"]'
          PASSING t.x AS "d")

CONTAINS(t.x, '(Big) OR (Street) INPATH
               (/P/LIs/LI/Description)') > 0

XMLExists('$d/P/LIs/LI/Description
           [. contains text "Big" ftor "Street"]'
          PASSING t.x AS "d")

CONTAINS(t.x, '({Big}) NOT ({Street}) INPATH
               (/P/LIs/LI/Description)') > 0

XMLExists('$d/P/LIs/LI/Description
           [. contains text
            "Big" ftand ftnot "Street"]'
          PASSING t.x AS "d")

CONTAINS(t.x, '({Street}) MNOT ({Big Street}) INPATH
               (/P/LIs/LI/Description)') > 0

XMLExists('$d/P/LIs/LI/Description
           [. contains text
            "Street" not in "Big Street"]'
          PASSING t.x AS "d")

CONTAINS(t.x, '(NEAR (({Big}, {Street}), 3) INPATH
               (/P/LIs/LI/Description)') > 0

XMLExists('$d/P/LIs/LI/Description
           [. contains text
            "Big" ftand "Street" window 3 words]'
          PASSING t.x AS "d")

XMLExists('declare namespace
           ipo="http://www.example.com/IPO";
           /ipo:P/ipo:LIs/ipo:LI/ipo:Description
           [. contains text "Big"]'
          PASSING t.x AS "d")

Table purchaseorder in sample database schema OE is stored object-relationally. Each purchase-order document has a single Reference element; this element is a singleton. You can thus use a shortcut to create an index on the underlying object-relational data.

CREATE INDEX po_reference_ix ON purchaseorder
  (XMLCast(XMLQuery ('$p/PurchaseOrder/Reference' PASSING po.OBJECT_VALUE AS "p"
                                                  RETURNING CONTENT)
              AS VARCHAR2(128)));

CREATE INDEX po_reference_ix ON purchaseorder
  (extractValue(OBJECT_VALUE, '/PurchaseOrder/Reference'));

In XMLType data stored object-relationally, a collection is stored as an ordered collection table (OCT) of an XMLType instance, which means that you can directly access its members. Because object-relational storage directly reflects the fine-grained structure of the XML data, you can create indexes that target individual collection members.