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 C-8.

Though you can create secondary indexes on path-table columns, you can generally ignore the path table itself.

You cannot access the path table, other than to DESCRIBE it and create (secondary) indexes on it. You need never explicitly gather statistics on the path table. You need only collect statistics on the XMLIndex index or the base table on which the XMLIndex index is defined; statistics are collected and maintained on the path table and its secondary indexes transparently.

A secondary index on column VALUE is used with XPath expressions in a WHERE clause that have predicates involving string matches. For example:

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

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

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.

You can, however, create a VALUE index of a different type. For example, you can create a number-valued index if that is appropriate for many of your queries. You can create multiple secondary indexes on the VALUE column. An index of a particular type is used only when it is appropriate. For example, a number-valued index is used only when the VALUE column is a number; it is ignored for other values. Secondary indexes on path-table columns are treated like any other secondary indexes — you can alter them, drop them, mark them unusable, and so on.

A few types of XPath expressions are not indexed by XMLIndex.

• Applications of XPath functions. 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).

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.

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 C-3 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 C-15 creates an XMLIndex index that is synchronized every Monday at 3:00 pm, starting tomorrow.

Example C-16 manually synchronizes the index created in Example C-15.

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 C-16, 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.

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