DB2 10.5 for Linux, UNIX, and Windows

Guidelines for matching indexes with queries overview

This section provides you with some guidelines and examples for matching queries with indexes over XML data.

Whether a query can make use of an index depends on whether the index or indexes you created are compatible with your query (also known as index matching), and whether the optimizer chooses to perform an index scan during query evaluation. The access plan of the Explain facility will tell you if query evaluation involved an index scan.

A query must meet at least the following conditions before it can use an index over XML data:

The data types in the query search condition match the indexed data types.
The query search condition includes a subset of the nodes that are indexed.

SQL and XQuery optimizer

The optimizer plans the evaluation of queries and chooses which indexes to use during evaluation. During query compilation, a query is matched with all the patterns in the XML index definitions to find candidate indexes that contain sufficient information to answer some part of the query.

The optimizer may take one of the following steps during query evaluation:

Scan the table containing the XML documents, without using an index
Use a relational index
Use relational index ANDing or index ORing
Use a new XML index operator
Use an index over XML data for the evaluation of a single XML pattern
Use index over XML data ANDing and ORing for the evaluation of complex XML patterns from a single query

Explain facility

The Explain facility can provide you with the access plan that is chosen to evaluate your query. When you look at an access plan, the following operators will tell you whether one or several indexes were using during query evaluation:

IXAND: ANDs the row IDs from two or more index scans.
XISCAN: Scans an index over XML data.
XANDOR: Allows ANDed predicates to be applied to multiple XML indexes.