5 Indexing XMLType Data

Problem: Fine-Grained Structure of XML Data

You can create indexes on one or more table 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.

Solution: XMLIndex

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 unstructured and hybrid storage of XML data, that is, CLOB storage. It does this by indexing the XML tags of your document and identifying 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.

Other Indexes for XML Data

In addition to XMLIndex, you can use function-based indexes and Oracle Text indexes with XML data. In releases prior to Oracle Database 11g Release 1 (11.1), CTXXPath indexes are also sometimes appropriate for use with XML data.

Creating Function-Based Indexes on Unstructured XMLType Tables and Columns

If a function-based index is defined on XML data that is not managed using structured storage, then the index is created by invoking the function on the XML content and indexing the result. (This is not necessarily what happens when you try to create a function-based index on XML data in structured storage — see "Creating Function-Based Indexes on Structured XMLType Tables and Columns".)

Example 5-1 shows the creation of an index based on SQL function extractValue, where the XML data is in unstructured (CLOB) storage. The data is first copied to CLOB-based table po_clob from structured-storage table purchaseorder of standard database schema OE. Both of these tables will be used in examples throughout this chapter.

CREATE TABLE po_clob OF XMLType
  XMLTYPE STORE AS CLOB
  ELEMENT "http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd#PurchaseOrder";
 
Table created.
 
INSERT INTO po_clob SELECT OBJECT_VALUE FROM OE.purchaseorder;
 
132 rows created.

CREATE UNIQUE INDEX po_fn_based_ix ON po_clob
  (extractValue(OBJECT_VALUE, '/PurchaseOrder/Reference'));
 
Index created.
 
INSERT INTO po_clob
  VALUES (XMLType(bfilename('XMLDIR', 'EABEL-20021009123335791PDT.xml'),
                  nls_charset_id('AL32UTF8')));
INSERT INTO po_clob
*
ERROR at line 1:
ORA-00001: unique constraint (OE.PO_FN_BASED_IX) violated

The cost-based optimizer considers using a function-based index only when the function that is included in the WHERE clause is identical to the function that was used to create the index.

To see this, consider the queries and explain plans in Example 5-2. These queries each find a purchase-order document based on the text node of element Reference. The first query, which uses function existsNode to locate the document, does not use the index created in Example 5-1; the second query, which uses function extractValue, does use the index. This is because the index was created using extractValue.

EXPLAIN PLAN FOR
  SELECT OBJECT_VALUE FROM po_clob
    WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Reference="EABEL-20021009123335791PDT"') = 1;
 
Explained.
 
--
SET ECHO OFF
 
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
Plan hash value: 2803800196
 
-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |    42 | 84084 |    23  (27)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| PO_CLOB |    42 | 84084 |    23  (27)| 00:00:01 |
-----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(EXISTSNODE(SYS_MAKEXML('0BAD982DA615296BE040578CB00B1198',
              3460,"PO_CLOB"."XMLDATA"),'/PurchaseOrder[Reference="EABEL-2002100912333
              5791PDT"')=1)
 
15 rows selected.
 
EXPLAIN PLAN FOR
  SELECT OBJECT_VALUE FROM po_clob
    WHERE extractValue(OBJECT_VALUE, '/PurchaseOrder/Reference') = 'EABEL-20021009123335791PDT';
 
Explained.
 
SET ECHO OFF
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
Plan hash value: 2594805861
 
----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     1 |  2002 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| PO_CLOB        |     1 |  2002 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PO_FN_BASED_IX |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access(EXTRACTVALUE(SYS_MAKEXML('0BAD982DA615296BE040578CB00B1198',3460,"XMLDAT
              A"),'/PurchaseOrder/Reference')='EABEL-20021009123335791PDT')
 
Note
-----
   - dynamic sampling used for this statement
 
19 rows selected.

Creating Function-Based Indexes on Structured XMLType Tables and Columns

When you use structured XMLType storage, there are a few things to be aware of when creating a function-based index:

• The element or attribute being targeted by the function must be a singleton, that is, it must occur only once in the XML Document — a function-based index must not target a collection. See "No XPath Rewrite for EXTRACTVALUE Applied to a Collection".

• If the function being indexed is extractValue, then Oracle XML DB tries to rewrite your function-based CREATE INDEX statement to a different CREATE INDEX statement that is not function-based and does not use the XPath-expression argument as such. Instead, the resulting index is a B-tree index that operates directly on the underlying objects. This B-tree index takes the place of the function-based index that you specify. See "XPath Rewrite for EXTRACTVALUE Indexes on Singleton Elements or Attributes".

• If Oracle XML DB cannot rewrite the CREATE INDEX statement, then a function-based index is created, just as for unstructured storage. This is what happens if the function is not extractValue, and it can also happen in a few cases for extractValue.

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

Index created.

SELECT INDEX_NAME, TABLE_NAME, COLUMN_NAME FROM USER_IND_COLUMNS
  WHERE INDEX_NAME = 'PO_FN_BASED_IX' AND TABLE_NAME = 'PURCHASEORDER';
 
INDEX_NAME       TABLE_NAME       COLUMN_NAME
---------------- ---------------- ----------------------
PO_FN_BASED_IX   PURCHASEORDER    "XMLDATA"."REFERENCE"
 
1 row selected.

CREATE INDEX po_fn_based_ix ON purchaseorder p (p."XMLDATA"."REFERENCE");

Index created.

SELECT INDEX_NAME, TABLE_NAME, COLUMN_NAME FROM USER_IND_COLUMNS
  WHERE INDEX_NAME = 'PO_FN_BASED_IX' AND TABLE_NAME = 'PURCHASEORDER';
 
INDEX_NAME       TABLE_NAME       COLUMN_NAME
---------------- ---------------- ----------------------
PO_FN_BASED_IX   PURCHASEORDER    "XMLDATA"."REFERENCE"
 
1 row selected.

CREATE INDEX po_fn_based_ix ON purchaseorder
  (extractValue(OBJECT_VALUE, '/PurchaseOrder/LineItems/LineItem/Part/@Id'));
CREATE INDEX po_fn_based_ix ON purchaseorder
*
ERROR at line 1:
ORA-19025: EXTRACTVALUE returns value of only one node

CREATE INDEX po_fn_based_ix
  ON purchaseorder (extract(OBJECT_VALUE,
                            'PurchaseOrder/LineItems/LineItem/Part/@Id').getStringVal());
 
Index created.

SELECT extract(OBJECT_VALUE, '/PurchaseOrder/LineItems') XML,
       extract(OBJECT_VALUE, 'PurchaseOrder/LineItems/LineItem/Part/@Id').getStringVal() INDEX_VALUE
  FROM purchaseorder
  WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1;
 
XML                                                               INDEX_VALUE
----------------------------------------------------------------- -----------------------------------
<LineItems>                                                       71551500905837429140222715515011020
  <LineItem ItemNumber="1">
    <Description>A Night to Remember</Description>
    <Part Id="715515009058" UnitPrice="39.95" Quantity="2"/>
  </LineItem>
  <LineItem ItemNumber="2">
    <Description>The Unbearable Lightness Of Being</Description>
    <Part Id="37429140222" UnitPrice="29.95" Quantity="2"/>
  </LineItem>
  <LineItem ItemNumber="3">
    <Description>Sisters</Description>
    <Part Id="715515011020" UnitPrice="29.95" Quantity="4"/>
  </LineItem>
</LineItems>

1 row selected.

Advantages of XMLIndex

An XMLIndex index can be used for SQL functions XMLQuery, XMLTable, XMLExists, XMLCast, extract, extractValue, and existsNode. It 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.

• XMLIndex can thus speed access to both SELECT list data and FROM list data, making it useful for XML fragment extraction, in particular. Function-based indexes (and CTXXPath indexes, which are deprecated) cannot be used to extract document fragments.

• You need no prior knowledge of the XPath expressions that will be used in queries. XMLIndex is completely general. This is not the case for function-based indexes. If you do have such prior knowledge, then you can often improve performance by tailoring XMLIndex indexing to those paths most queried.

• You can use an XMLIndex index with either XML schema-based or non-schema-based data. It can be used with unstructured storage, hybrid storage, and binary XML storage. B-tree indexing is appropriate only for XML schema-based data that is stored object-relationally (structured storage); it is ineffective for XML schema-based data stored in a CLOB instance.

• For hybrid storage of XML schema-based data, XMLIndex can handle XPath expressions that target document fragments that are stored within a CLOB instance. XPath rewrite is ineffective in such cases.

• 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.

• 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 (and CTXXPATH indexes, which are deprecated).

• Updating of XMLIndex indexes on binary XML storage can be accomplished in a piecewise manner, improving DML performance considerably. This is not the case for any of the other kinds of indexes you might use with XML data.

XPath Expressions Not Indexed by XMLIndex

The following types of XPath expressions are not indexed by XMLIndex:

• Applications of XPath functions, except ora:contains. In particular, user-defined XPath functions are not indexed.

• Axes other than child, descendant, and attribute, that is, axes parent, ancestor, following-sibling, preceding-sibling, following, preceding, and ancestor-or-self.

• Expressions using the union operator, | (vertical bar).

Components of an XMLIndex Index

XMLIndex is a domain index; it is designed specifically for the domain of XML data. It is a logical index, which has three components:

• 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).

XMLIndex is implemented using a path table and a set of (local) secondary indexes corresponding to its components. These are all owned by the owner of the base table upon which the XMLIndex index is created. 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.

Table 5-2 shows the main information^Foot 1  that is in the path table. The path index and the order index each use two columns of the path table. Together, columns PATHID and RID represent the path index; columns ORDER_KEY and RID represent the order index. Secondary indexes are created automatically for columns PATHID and ORDER_KEY.

Example 5-8 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>

/PurchaseOrder[Reference/text() = "SBELL-2002100912333601PDT"]

substr(VALUE, 1 800) = substr(:1, 1, 800) AND VALUE = :1;

Data Dictionary Static Public Views Related to XMLIndex

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. Table 5-3 describes the columns in each of these views.

Creating, Dropping, Altering, and Examining an XMLIndex Index

You create an XMLIndex index by declaring the index type to be XDB.XMLIndex, as illustrated in Example 5-9.

CREATE INDEX po_xmlindex_ix ON po_clob (OBJECT_VALUE) INDEXTYPE IS XDB.XMLIndex;
 
Index created.

This creates an XMLIndex index named po_xmlindex_ix on XMLType table po_clob.

You can create an XMLIndex index on CLOB portions of hybrid XMLType storage, that is, on CLOB data that is embedded within object-relational storage. Example 5-10 illustrates this. It assumes that the XML schema used maps the LineItems element to CLOB, such as is shown in Example 5-10.

CREATE INDEX po_xmlindex_hybrid_ix ON li_clob
  (extract(OBJECT_VALUE, '/PurchaseOrder/LineItems'))
  INDEXTYPE IS XDB.XMLIndex;

<xs:element name="LineItems" type="LineItemsType" xdb:SQLName="LINEITEMS"                 
           xdb:SQLType="CLOB"/>

You can obtain the name of an XMLIndex index on a particular XMLType table (or column), as shown in Example 5-12. You can also select INDEX_NAME from DBA_INDEXES or ALL_INDEXES, as appropriate.

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

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

You rename or drop an XMLIndex index just as you would any other index, as illustrated in Example 5-13. This renaming changes the name of the XMLIndex index only. It does not change the name of the path table — you can rename the path table separately.

ALTER INDEX po_xmlindex_ix RENAME TO new_name_ix;

Index altered.

DROP INDEX new_name_ix;

Index dropped.

Similarly, you can change other index properties using other ALTER INDEX options, such as REBUILD. XMLIndex is no different from other index types in this respect.

You can use the PARAMETERS clause of a CREATE INDEX statement to name the path table. Example 5-14 names the path table "my_path_table".

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

If you do not name the path table this way, then its name is generated by the system, using the index name you provide to CREATE INDEX as a base. Example 5-15 shows this for the index created in Example 5-9.

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

By default, the storage options of the XMLIndex path table and its secondary indexes are derived from the storage properties of the base table on which the XMLIndex index is created. You can specify different storage options by using a PARAMETERS clause when you create the index, as shown in Example 5-16. The PARAMETERS clause of CREATE INDEX (and ALTER INDEX) must be between single quotation marks (').

CREATE INDEX po_xmlindex_ix ON po_clob (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)
      PATH ID INDEX   po_path_id_ix (LOGGING PCTFREE 1 INITRANS 3)
      ORDER KEY INDEX po_order_key_ix (LOGGING PCTFREE 1 INITRANS 3)
      VALUE INDEX     po_value_ix (LOGGING PCTFREE 1 INITRANS 3)');
 
Index created.

Because XMLIndex is a logical domain index, not a physical index, all physical attributes are either zero (0) or NULL.

In addition to specifying storage options for the path table, Example 5-16 names the secondary indexes. An index is created on column VALUE because it is specified; otherwise, no such index would be created.

Like the name of the path table, the names of the secondary indexes on the path-table columns are generated automatically using the index name as a base, unless you specify them in the PARAMETERS clause. Example 5-17 illustrates this, and shows how you can determine these names using public view USER_IND_COLUMNS. It also shows that the path index and the order index each use two columns, including column RID. Note, too, that no VALUE index was created, by default.

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
------------------------------ ------------ ---------------
SYS73321_PO_XMLINDE_ORDKEY_IX  ORDER_KEY                  2
SYS73321_PO_XMLINDE_ORDKEY_IX  RID                        1
SYS73321_PO_XMLINDE_PATHID_IX  PATHID                     1
SYS73321_PO_XMLINDE_PATHID_IX  RID                        2
 
4 rows selected.

The RENAME clause of an ALTER INDEX statement for XMLIndex applies only to the XMLIndex index itself. To rename the path table and secondary indexes, you must determine the names of these objects and use appropriate ALTER INDEX statements on them directly. Similarly, to retrieve the physical properties of the secondary indexes or alter them in any other way, you will need to obtain their names, as in Example 5-17.

You can use ALTER INDEX to modify any of the index parameters of the primary or secondary indexes . As a convenience, an alternative way to change properties of a path-id, order-key, or value index is to use ALTER INDEX on the parent XMLIndex index, providing a PARAMETERS clause with the new properties.

Creating Additional Secondary Indexes on an XMLIndex Path Table

This section adds extra secondary indexes to the XMLIndex index created in Example 5-16.

You can create any number of additional secondary indexes on the VALUE column of the path table of an XMLIndex index. These can be of different types, including function-based indexes and Oracle Text indexes.

Whether or not a given index is used for a given element occurrence when processing a query is determined by whether it is of the appropriate type for that value and whether it is cost-effective to use it.

Example 5-18 creates a function-based index on column VALUE of the path table using SQL function substr. This might be useful if your queries often use substr applied to the text nodes of XML elements.

CREATE INDEX fn_based_ix ON po_path_table (substr(VALUE, 1, 100));
 
Index created.

If you have many elements whose text nodes represent numeric values, then it can make sense to create a numeric index on the column VALUE. However, doing so directly, in a manner analogous to Example 5-18, raises an ORA-01722 error (invalid number) if some of the element values are not numbers. This is illustrated in Example 5-19.

CREATE INDEX direct_num_ix ON po_path_table (to_number(VALUE));
CREATE INDEX direct_num_ix ON po_path_table (to_number(VALUE))
                                             *
ERROR at line 1:
ORA-01722: invalid number

What is needed is an index that will be used for numeric-valued elements but will be ignored for element occurrences that do not have numeric values. Procedure createNumberIndex of package DBMS_XMLINDEX exists specifically for this purpose. You pass it the names of the database schema, the XMLIndex index, and the numeric index to be created. Creation of a numeric index is illustrated in Example 5-20.

CALL DBMS_XMLINDEX.createNumberIndex('OE', 'PO_XMLINDEX_IX', 'API_NUM_IX');

Note that because such an index is specifically designed to ignore elements that do not have numeric values, its use will not detect their presence. If there are non-numeric elements and, for whatever reason, the XMLIndex index is not used in some query, then an ORA-01722 error will be raised. However, if the index is used, no such error will be raised, because the index ignores non-numeric data. As always, the use of an index will never change the result set — it will never give you different results, but use of an index can prevent you from detecting erroneous data.

Creating a date-valued index is similar to creating a numeric index; you use procedure DBMS_XMLINDEX.createDateIndex. Example 5-21 shows this.

CALL DBMS_XMLINDEX.createDateIndex('OE', 'PO_XMLINDEX_IX', 'API_DATE_IX', 
                                   'dateTime');

Example 5-22 creates an Oracle Text CONTEXT index on column VALUE. This is useful for full-text queries on text values of XML elements. XPath predicates that use XPath function ora:contains are rewritten to applications of SQL function contains on column VALUE. If a CONTEXT index is defined on column VALUE, then it will be used during predicate evaluation. An Oracle Text index is independent of all other VALUE-column indexes.

CREATE INDEX po_otext_ix ON po_path_table (VALUE)
  INDEXTYPE IS CTXSYS.CONTEXT
    PARAMETERS('TRANSACTIONAL');
 
Index created.

The query in Example 5-23 shows all of the secondary indexes created on the path table of an XMLIndex index. The indexes created explicitly are in bold. Note, in particular, that some indexes, such as the function-based index created on column VALUE, do not appear as such; the column name listed for it them a system-generated name, such as SYS_NC00006$. This means that you cannot see these columns by executing a query with COLUMN_NAME = 'VALUE' in the WHERE clause.

SELECT c.INDEX_NAME, c.COLUMN_NAME, c.COLUMN_POSITION, e.COLUMN_EXPRESSION
  FROM USER_IND_COLUMNS c LEFT OUTER JOIN USER_IND_EXPRESSIONS e
    ON (c.INDEX_NAME = e.INDEX_NAME)
  WHERE c.TABLE_NAME IN (SELECT PATH_TABLE_NAME FROM USER_XML_INDEXES
                           WHERE INDEX_NAME = 'PO_XMLINDEX_IX')
  ORDER BY c.INDEX_NAME, c.COLUMN_NAME;
 
INDEX_NAME           COLUMN_NAME  COLUMN_POSITION COLUMN_EXPRESSION
-------------------- ------------ --------------- ----------------------
API_DATE_IX          SYS_NC00008$               1 SYS_EXTRACT_UTC(SYS_XMLCONV("V
                                                  ALUE",3,8,0,0,181))
API_NUM_IX           SYS_NC00007$               1 TO_BINARY_DOUBLE("VALUE")
FN_BASED_IX          SYS_NC00006$               1 SUBSTR("VALUE",1,100)
PO_ORDER_KEY_IX      ORDER_KEY                  2
PO_ORDER_KEY_IX      RID                        1
PO_OTEXT_IX          VALUE                      1
PO_PATH_ID_IX        PATHID                     1
PO_PATH_ID_IX        RID                        2
PO_VALUE_IX          VALUE                      1
 
9 rows selected.

How to Tell If XMLIndex is Used

It is at query compile time that it is determined whether or not a given XMLIndex index can be used, that is, whether the query can be rewritten into a query against the index. 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 indexing, then an XMLIndex  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 will be present in the data.

To know whether a particular XMLIndex index has been used in resolving a query, you can examine an explain plan of the query. If the index is used, then its path table, order key, or path id will be referenced in the explain plan. The explain plan will not directly indicate that a domain index was used; it will not refer to the XMLIndex index by name.

Example 5-24 shows that the XMLIndex index created in Example 5-14 is used in a particular query.

SET AUTOTRACE ON EXPLAIN
 
SELECT XMLQuery('/PurchaseOrder/Requestor' PASSING OBJECT_VALUE RETURNING CONTENT) FROM po_clob
  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 |   15  (7)| 00:00:01 |
|   1 |  SORT GROUP BY                   |                               |     1 |  3524 |          |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID    | MY_PATH_TABLE                 |     2 |  7048 |    2  (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN              | SYS55148_PO_XMLINDE_PATHID_IX |     6 |       |    1  (0)| 00:00:01 |
|   4 |  NESTED LOOPS                    |                               |     1 |    24 |   15  (7)| 00:00:01 |
|   5 |   VIEW                           | VW_SQ_1                       |     1 |    12 |   13  (0)| 00:00:01 |
|   6 |    HASH UNIQUE                   |                               |     1 |  5046 |          |          |
|   7 |     NESTED LOOPS                 |                               |       |       |          |          |
|   8 |      NESTED LOOPS                |                               |     1 |  5046 |   13  (0)| 00:00:01 |
|*  9 |       TABLE ACCESS BY INDEX ROWID| MY_PATH_TABLE                 |     1 |  3524 |   11  (0)| 00:00:01 |
|* 10 |        INDEX RANGE SCAN          | SYS55148_PO_XMLINDE_PATHID_IX |     1 |       |    2  (0)| 00:00:01 |
|* 11 |       INDEX RANGE SCAN           | SYS55148_PO_XMLINDE_PATHID_IX |     6 |       |    1  (0)| 00:00:01 |
|* 12 |      TABLE ACCESS BY INDEX ROWID | MY_PATH_TABLE                 |     1 |  1522 |    2  (0)| 00:00:01 |
|  13 |   TABLE ACCESS BY USER ROWID     | PO_CLOB                       |     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"."PATHID"=HEXTORAW('74C39DFE')  AND "SYS_P0"."RID"=:B1)
   9 - filter("SYS_P5"."PATHID"=HEXTORAW('6F7C')  AND SYS_XMLI_LOC_ISNODE("SYS_P5"."LOCATOR")=1)
  10 - access("SYS_P5"."VALUE"='SBELL-2002100912333601PDT')
  11 - access("SYS_P2"."PATHID"=HEXTORAW('093CA37E')  AND "SYS_P5"."RID"="SYS_P2"."RID")
  12 - filter(SYS_XMLI_LOC_ISNODE("SYS_P2"."LOCATOR")=1 AND "SYS_P2"."ORDER_KEY"<"SYS_P5"."ORDER_KEY" AND
              "SYS_P5"."ORDER_KEY"<SYS_ORDERKEY_MAXCHILD("SYS_P2"."ORDER_KEY") AND
              SYS_ORDERKEY_DEPTH("SYS_P2"."ORDER_KEY")+1=SYS_ORDERKEY_DEPTH("SYS_P5"."ORDER_KEY"))
. . .

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

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

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 functions XMLQuery, XMLTable, XMLExists, XMLCast, extractValue, existsNode, and extract. Unlike function-based indexes (and CTXXPath indexes, which are deprecated), XMLIndex indexes can be used when you extract an XML fragment from a document. Example 5-26 illustrates this.

SET AUTOTRACE ON EXPLAIN
 
SELECT li.description, li.itemno
  FROM po_clob, XMLTable('/PurchaseOrder/LineItems/LineItem'
                         PASSING OBJECT_VALUE
                         COLUMNS "DESCRIPTION" VARCHAR(40) PATH '/LineItem/Description',
                                 "ITEMNO"      INTEGER     PATH '/LineItem/@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 | 8  (13)| 00:00:01 |
|*  1 |  FILTER                            |                               |       |       |        |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID      | MY_PATH_TABLE                 |     1 |  3524 | 2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                | SYS63727_PO_XMLINDE_PATHID_IX |     6 |       | 1   (0)| 00:00:01 |
|*  4 |  FILTER                            |                               |       |       |        |          |
|*  5 |   TABLE ACCESS BY INDEX ROWID      | MY_PATH_TABLE                 |     1 |  3524 | 2   (0)| 00:00:01 |
|*  6 |    INDEX RANGE SCAN                | SYS63727_PO_XMLINDE_PATHID_IX |     1 |       | 1   (0)| 00:00:01 |
. . .
|* 14 |         TABLE ACCESS BY INDEX ROWID| MY_PATH_TABLE                 |     1 |  3524 | 2   (0)| 00:00:01 |
|* 15 |          INDEX RANGE SCAN          | SYS63727_PO_XMLINDE_VALUE_IX  |     1 |       | 1   (0)| 00:00:01 |
|* 16 |         INDEX RANGE SCAN           | SYS63727_PO_XMLINDE_ORDKEY_IX |     1 |       | 1   (0)| 00:00:01 |
|* 17 |        TABLE ACCESS BY INDEX ROWID | MY_PATH_TABLE                 |     1 |  1522 | 2   (0)| 00:00:01 |
|  18 |     TABLE ACCESS BY USER ROWID     | PO_CLOB                       |     1 |    12 | 1   (0)| 00:00:01 |
|* 19 |    INDEX RANGE SCAN                | SYS63727_PO_XMLINDE_PATHID_IX |     6 |       | 1   (0)| 00:00:01 |
|* 20 |   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_P3"."ORDER_KEY">:B1 AND SYS_XMLI_LOC_ISNODE("SYS_P3"."LOCATOR")=1 AND
              "SYS_P3"."ORDER_KEY"<SYS_ORDERKEY_MAXCHILD(:B2) AND SYS_ORDERKEY_DEPTH("SYS_P3"."ORDER_KEY")=
              SYS_ORDERKEY_DEPTH(:B3)+1)
   3 - access("SYS_P3"."PATHID"=HEXTORAW('54393E4C')  AND "SYS_P3"."RID"=:B1)
   4 - filter(:B1<SYS_ORDERKEY_MAXCHILD(:B2))
   5 - filter("SYS_P6"."ORDER_KEY">:B1 AND SYS_XMLI_LOC_ISNODE("SYS_P6"."LOCATOR")=1 AND
              "SYS_P6"."ORDER_KEY"<SYS_ORDERKEY_MAXCHILD(:B2) AND SYS_ORDERKEY_DEPTH("SYS_P6"."ORDER_KEY")=
              SYS_ORDERKEY_DEPTH(:B3)+1)
   6 - access("SYS_P6"."PATHID"=HEXTORAW('7DE452AA')  AND "SYS_P6"."RID"=:B1)
       filter(SYS_PATHID_IS_NMSPC("SYS_P6"."PATHID")=0)
  14 - filter("SYS_P11"."PATHID"=HEXTORAW('6F7C')  AND SYS_XMLI_LOC_ISNODE("SYS_P11"."LOCATOR")=1)
  15 - access("SYS_P11"."VALUE"='SBELL-2002100912333601PDT')
  16 - access("SYS_P11"."RID"="SYS_P8"."RID" AND "SYS_P8"."ORDER_KEY"<"SYS_P11"."ORDER_KEY")
  17 - filter("SYS_P8"."PATHID"=HEXTORAW('093CA37E')  AND SYS_XMLI_LOC_ISNODE("SYS_P8"."LOCATOR")=1)
  19 - access("SYS_P0"."PATHID"=HEXTORAW('7676FDEA')  AND "SYS_P0"."RID"="PO_CLOB".ROWID)
  20 - filter(SYS_XMLI_LOC_ISNODE("SYS_P0"."LOCATOR")=1)
. . .

Turning Off Use of XMLIndex

You can turn off the use of XMLIndex in any of these ways:

• Use optimizer hint /*+ NO_XMLINDEX_REWRITE */

• Use optimizer hint /*+ NO_XMLINDEX_REWRITE_IN_SELECT */

• Use optimizer hint /*+ NO_XML_QUERY_REWRITE */

Each of these turns off the use of all XMLIndex indexes. In addition to turning off use of XMLIndex, NO_XML_QUERY_REWRITE turns off all XPath rewrite (XMLIndex is part of XPath rewrite).

Hint NO_XMLINDEX_REWRITE_IN_SELECT turns off the use of XMLIndex indexes only for XPath expressions in the SELECT list; XMLIndex indexes can still be used for XPath expressions in other query parts, such as a WHERE clause or a FROM clause. This hint can be especially useful with XML data that is stored as binary XML, in cases where streaming evaluation of XPath expressions in a SELECT list provides better performance than XMLIndex.

Example 5-27 shows the use of these optimizer hints.

SELECT /*+ NO_XMLINDEX_REWRITE */ 
  count(*) FROM po_clob WHERE existsNode(OBJECT_VALUE, '/*') = 1;

SELECT /*+ NO_XMLINDEX_REWRITE_IN_SELECT */
  extractValue(li.OBJECT_VALUE, '/LineItem/Description')
  FROM po_clob p,
       table(XMLSequence(extract(p. OBJECT_VALUE,
                                 '/PurchaseOrder/LineItems/LineItem'))) li;

SELECT /*+ NO_XMLINDEX_REWRITE_IN_SELECT */
  li.description
  FROM po_clob p,
       XMLTable('/PurchaseOrder/LineItems/LineItem' PASSING OBJECT_VALUE
                COLUMNS "DESCRIPTION" VARCHAR(40)
                PATH '/LineItem/Description') li;

SELECT /*+ NO_XML_QUERY_REWRITE */
  count(*) FROM po_clob WHERE existsNode(OBJECT_VALUE, '/*') = 1;

In each of the queries that uses hint NO_XMLINDEX_REWRITE_IN_SELECT, the XPath expression in the SELECT list does not use XMLIndex, but the XPath expression in the FROM clause, /PurchaseOrder/LineItems/LineItem, might use XMLIndex. Note that in the query that uses function XMLTable, the XPath expression that corresponds to column li.description does not appear in the SELECT list textually, but it is treated as if it does because of XPath rewrite. That is, XPath rewrite treats the XPath expression as if it were present in the SELECT list.

XMLIndex Path Subsetting: Specifying the Paths You Want to Index

One of the advantages of XMLIndex is that it is very general: you need not specify which XPath locations to index; you need no prior knowledge of the XPath expressions that will be queried. By default, XMLIndex indexes all possible XPath locations in your XML data.

However, if you are aware of the XPath expressions that you are most likely to query, you can narrow the focus of XMLIndex indexing and thus improve performance. Having fewer unnecessary indexes means that less space is required for indexing, which improves index maintenance during DML operations. Having fewer indexed nodes improves DDL performance, and having a smaller path table improves query performance.

You narrow the focus of indexing by pruning the set of XPath expressions (paths) corresponding to XML fragments to be indexed, specifying a subset of all possible paths. You can do this in two alternative ways:

• Exclusion – Start with the default behavior of including all possible XPath expressions, and exclude some of them from indexing.

• Inclusion – Start with an empty set of XPath expressions to be used in indexing, and add paths to this inclusion set.

You can specify path subsetting either when you create an XMLIndex index using CREATE INDEX or when you modify it using ALTER INDEX. In both cases, you provide the subsetting information in the PATHS parameter of the statement's PARAMETERS clause. For exclusion, you use keyword EXCLUDE. For inclusion, you use keyword INCLUDE for ALTER INDEX and no keyword for CREATE INDEX (list the paths to include). You can also specify namespace mappings for the nodes targeted by the PATHS parameter.

For ALTER INDEX, keyword INCLUDE or EXCLUDE is followed by keyword ADD or REMOVE, to indicate whether the list of paths that follows the keyword is to be added or removed from the inclusion or exclusion list. For example, this statement adds path /PurchaseOrder/Reference to the list of paths to be excluded from indexing:

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

To alter an XMLIndex index so that it includes all possible paths, you can use keyword ALL in place of an explicit list of paths – use either EXCLUDE REMOVE (ALL) or INCLUDE ADD (ALL); they are equivalent. (You cannot exclude all paths.)

CREATE INDEX po_xmlindex_ix ON po_clob (OBJECT_VALUE) INDEXTYPE IS XDB.XMLINDEX
  PARAMETERS ('PATHS (INCLUDE (/PurchaseOrder/LineItems//* 
                               /PurchaseOrder/Reference))');

ALTER INDEX po_xmlindex_ix REBUILD
  PARAMETERS ('PATHS (INCLUDE ADD (/PurchaseOrder/Requestor 
                                   /PurchaseOrder/Actions/Action//*))');

CREATE INDEX po_xmlindex_ix ON po_clob (OBJECT_VALUE) INDEXTYPE IS XDB.XMLIndex
  PARAMETERS ('PATHS INCLUDE (/PurchaseOrder/LineItems//*   /PurchaseOrder/ipo:Reference)
                     NAMESPACE MAPPING (xmlns="http://xmlns.oracle.com"
                                        xmlns:ipo="http://xmlns.oracle.com/ipo"))');

Using XMLIndex on Oracle XML DB Repository

A database administrator (DBA) can create an XMLIndex index on resources in Oracle XML DB Repository to improve querying of XML data or metadata (system-defined or user-defined).

CALL DBMS_XDB_ADMIN.DropRepositoryXMLIndex();

SELECT ANY_PATH FROM RESOURCE_VIEW
  WHERE XMLExists('declare namespace r = "http://xmlns.oracle.com/xdb/XDBResource.xsd"; (: :)
                   /r:Resource/r:Contents/PurchaseOrder[Reference="TFOX-20021009123335520PDT"]'
                  PASSING RES)
    AND under_path (RES, '/home/OE/PurchaseOrders/2002/') = 1;
 
ANY_PATH
--------------------------------------------------------------
/home/OE/PurchaseOrders/2002/Jan/TFOX-20021009123335520PDT.xml
 
1 row selected.

SELECT ANY_PATH FROM RESOURCE_VIEW
  WHERE XMLExists('declare namespace r = "http://xmlns.oracle.com/xdb/XDBResource.xsd"; (: :)
                   /r:Resource/r:CreationDate/text()=xs:dateTime("2005-02-07T18:31:53.093179")'
                  PASSING RES)
   AND under_path (RES, '/home/OE/PurchaseOrders/2002/') = 1;
 
ANY_PATH
-----------------------------------------------------------------
/home/OE/PurchaseOrders/2002/Apr
/home/OE/PurchaseOrders/2002/Apr/AMCEWEN-20021009123336171PDT.xml
/home/OE/PurchaseOrders/2002/Apr/AMCEWEN-20021009123336271PDT.xml
/home/OE/PurchaseOrders/2002/Apr/EABEL-20021009123336251PDT.xml
/home/OE/PurchaseOrders/2002/Apr/PTUCKER-20021009123336191PDT.xml
/home/OE/PurchaseOrders/2002/Apr/PTUCKER-20021009123336291PDT.xml
. . .

144 rows selected.

CALL DBMS_XDB_ADMIN.DropRepositoryXMLIndex();

XMLIndex Parallelism

You can use a PARALLEL clause (with optional degree) when creating or altering an XMLIndex index, to ensure that the index creation and maintenance are carried out in parallel. This can improve the performance of index creation and maintenance. It can also consume more storage, because storage parameters apply separately to each query server process. For example, an index created with an INITIAL value of 5M and a parallelism degree of 12 consumes at least 60M of storage during index creation.

The syntax for the parallelism clause for CREATE INDEX and ALTER INDEX is the same as for other domain indexes:

{ NOPARALLEL | PARALLEL [ integer ] }

Example 5-33 creates an XMLIndex index with a parallelism degree of 10.

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

In Example 5-33, the path table and the secondary indexes are created with the same parallelism degree as the XMLIndex index itself, 10, by inheritance. You can specify different parallelism degrees for these by using separate PARALLEL clauses. Example 5-34 demonstrates this.

CREATE INDEX po_xmlindex_ix ON sale_info (sale_po_clob) INDEXTYPE IS XDB.XMLIndex 
  PARAMETERS ('PATH TABLE po_path_table (PARALLEL 10)
               PATH ID INDEX po_pathid_ix
               ORDER KEY INDEX po_orderkey_ix (PARALLEL 5)') NOPARALLEL;

Asynchronous (Deferred) Maintenance of XMLIndex Indexes

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 will 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 5-4 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 ASYNC is ALWAYS; otherwise it has value TRUE. Specifying an explicit STALE value that contradicts this rule raises an error.

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

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

Example 5-36 manually synchronizes the index created in Example 5-35.

EXEC DBMS_XMLINDEX.SyncIndex('OE', 'PO_XMLINDEX_IX');

When XMLIndex index synchronization is deferred, all DML changes (inserts, updates, deletions) made to the base table since the last index synchronization are recorded in a 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, the more the index is likely to be in need of synchronization.

Collecting Statistics on XMLIndex Objects For the Cost-Based Optimizer

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.

For XMLIndex, you normally need to collect statistics on only the base table on which the XMLIndex index is defined (using, for example, procedure DBMS_STATS.gather_table_stats). This automatically collects statistics for the XMLIndex index itself, as well as the path table and its secondary indexes. If you delete statistics on the base table (using procedure DBMS_STATS.delete_table_stats), then statistics on the other objects are also deleted. Similarly, if you collect statistics on the XMLIndex index (using procedure DBMS_STATS.gather_index_stats), then statistics are also collected on the path table and its secondary indexes.

Example 5-37 collects statistics on the base table po_clob. Statistics are automatically collected on the XMLIndex index, its path table, and the secondary path-table indexes.

CALL DBMS_STATS.gather_table_stats(USER, 'PO_CLOB');

Guidelines for Using XMLIndex

The following are some guidelines for using XMLIndex. 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.

• Be aware that if an XPath expression indicates that the full XMLType table or column is needed anyway, then an XMLIndex will not be used, since it would not improve performance. This is the case, for instance, if you access the document root (/), as in this query:

  SELECT COUNT(*) FROM po_clob WHERE existsNode(OBJECT_VALUE, '/') = 1;

• When you expect a single result, use extractValue instead of extract plus method getStringVal(), so that an index on column VALUE of the path table can be used. (You must thus provide keyword VALUE in the PARAMETERS clause when you create the XMLIndex index.)

• Use count(*), not count(extractValue(…)) in a SELECT clause, when possible. 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_clob, XMLTable('//LineItem' PASSING OBJECT_VALUE);
  

  Do not use this:

  SELECT count(li.value)
   FROM po_clob p, XMLTable('//LineItem' PASSING p.OBJECT_VALUE
                            COLUMNS value VARCHAR2(30) PATH '/LineItem/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_clob p,
         XMLTable('PurchaseOrder/LineItems/LineItem' PASSING p.OBJECT_VALUE
                  COLUMNS description VARCHAR2(256)
                  PATH '/LineItem/Description') li;
  

  Do not use this:

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

• If you use an XPath expression in a query to drill down inside a virtual table (created, for example, using SQL function XMLTable), then create a secondary index on the order key of the path table using 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_clob p,
         XMLTable('PurchaseOrder/LineItems/LineItem' PASSING p.OBJECT_VALUE
                  COLUMNS description VARCHAR2(256)
                  PATH '/LineItem/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 5-26 for an example that shows the use of sys_orderkey_depth

PARAMETERS Clause for CREATE INDEX and ALTER INDEX

This section presents the syntax for the PARAMETERS clause of SQL statements CREATE INDEX and ALTER INDEX for use with XMLIndex.

PARAMETERS Clause Syntax for CREATE INDEX and ALTER INDEX

The PARAMETERS clause is PARAMETERS ('XMLIndex_parameters'), where XMLIndex_parameters is one or more repetitions of XMLIndex_parameter_clause:

XMLIndex_parameters ::=

Description of the illustration xmlindex_parameters.gif

XMLIndex_parameter_clause ::=

Description of the illustration xmlindex_parameter_clause.gif

create_index_paths_clause ::=

Description of the illustration create_index_paths_clause.gif

alter_index_paths_clause ::=

Description of the illustration alter_index_paths_clause.gif

namespace_mapping_clause ::=

Description of the illustration namespace_mapping_clause.gif

path_table_clause ::=

Description of the illustration path_table_clause.gif

path_id_clause ::=

Description of the illustration path_id_clause.gif

order_key_clause ::=

Description of the illustration order_key_clause.gif

xml_index_value_clause ::=

Description of the illustration xmlindex_value_clause.gif

Creating and Using Oracle Text Indexes

To create an Oracle Text index, use CREATE INDEX, specifying the INDEXTYPE as CTXSYS.CONTEXT, as illustrated in Example 5-38.

CREATE INDEX po_otext_ix ON po_clob (OBJECT_VALUE) INDEXTYPE IS CTXSYS.CONTEXT;
 
Index created.

You can also perform Oracle Text operations such as contains and score on XMLType columns. Example 5-39 shows an Oracle Text search using SQL function contains.

SELECT DISTINCT extractValue(OBJECT_VALUE, 
                             '/PurchaseOrder/ShippingInstructions/address') "Address"
  FROM po_clob
  WHERE contains(OBJECT_VALUE,
                 '$(Fortieth) INPATH (PurchaseOrder/ShippingInstructions/address)') > 0;
 
Address
------------------------------
1200 East Forty Seventh Avenue
New York
NY
10024
USA
 
1 row selected.

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
Plan hash value: 274475732
 
--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |     7 | 14098 |    10  (10)| 00:00:01 |
|   1 |  HASH UNIQUE                 |             |     7 | 14098 |    10  (10)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| PO_CLOB     |     7 | 14098 |     9   (0)| 00:00:01 |
|*  3 |    DOMAIN INDEX              | PO_OTEXT_IX |       |       |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("CTXSYS"."CONTAINS"(SYS_MAKEXML('2B0A2483AB140B35E040578C8A173FEC',523
              3,"XMLDATA"),'$(Fortieth) INPATH (PurchaseOrder/ShippingInstructions/address)')>0)
 
20 rows selected.

Oracle Text Indexes Are Used Independently of Other Indexes

Oracle Text indexing is completely orthogonal to the other types of indexing described in this chapter. Whenever SQL function contains or XPath function ora:contains is used, an Oracle Text index can be used for full-text search.

Example 5-40 demonstrates this in the case where both an XMLIndex index and an Oracle Text index are defined on the same XML data. The query is the same as in Example 5-39. The Oracle Text index is created on the VALUE column of the XMLIndex path table of Example 5-14.

CREATE INDEX po_otext_ix ON my_path_table (VALUE) INDEXTYPE IS CTXSYS.CONTEXT;
 
Index created.

EXPLAIN PLAN FOR
  SELECT DISTINCT extractValue(OBJECT_VALUE, '/PurchaseOrder/ShippingInstructions/address') "Address"
    FROM po_clob
    WHERE contains(OBJECT_VALUE, '$(Fortieth) INPATH (PurchaseOrder/ShippingInstructions/address)') > 0;
 
Explained.
 
--
SET ECHO OFF;
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------
Plan hash value: 2664483039
 
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                               |     1 |  2014 |     3  (34)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| MY_PATH_TABLE                 |     1 |  3524 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | SYS78942_PO_XMLINDE_ORDKEY_IX |     1 |       |     2   (0)| 00:00:01 |
|   3 |  HASH UNIQUE                |                               |     1 |  2014 |     3  (34)| 00:00:01 |
|*  4 |   TABLE ACCESS FULL         | PO_CLOB                       |     1 |  2014 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("SYS_P0"."PATHID"=HEXTORAW('35EF580A')  AND SYS_XMLI_LOC_ISNODE("SYS_P0"."LOCATOR")=1)
   2 - access("SYS_P0"."RID"=:B1)
       filter("SYS_P0"."RID"=:B1)
   4 - filter("CTXSYS"."CONTAINS"(SYS_MAKEXML("PO_CLOB"."XMLDATA"),'$(Fortieth) INPATH
              (PurchaseOrder/ShippingInstructions/address)')>0)
 
Note
-----
   - dynamic sampling used for this statement
 
24 rows selected.

The explain plan in Example 5-40 references both the XMLIndex index and the Oracle Text index, indicating that both are used.

• The XMLIndex index is indicated by its path table, MY_PATH_TABLE, and its order-key index, SYS78942_PO_XMLINDE_ORDKEY_IX.

• The Oracle Text index is indicated by the reference to SQL function contains in the predicate information.