Access methods with XML indexes
Several data access methods use XML indexes.
When you create a table with XML columns, Db2 implicitly creates a document ID index (DOCID index) on the base table and a node ID index (NODEID index) on each associated XML table. The document ID index associates base table rows with rows to which XML indexes point. The data for a document in an XML table is stored in multiple records. A node ID index links the records for an XML document.
When you explicitly create an index on an XML column, the XML index contains composite key values that map XML values to DOCID and NODEID pairs. The XML index indexes the nodes in an XML document that match an XPath expression in the index definition. Db2 compares an XPath expression in a predicate to the XPath expression in an XML index to determine index key entries that contain matched key values. Db2 uses the DOCIDs from the DOCID and NODEID pairs of the identified index key entries to locate the corresponding base table rows efficiently.
The following data access methods are used for predicates that have eligible XML indexes.
| Access method name | ACCESSTYPE value in PLAN_TABLE | Purpose |
|---|---|---|
| DOCID list access | DX | Retrieval of base table rows that correspond to XML table rows. Db2 searches an XML index, retrieves all the qualified DOCIDs, and creates a DOCID list. Db2 uses the DOCID index to convert the DOCID list to a RID list that it uses to fetch base table rows. If certain conditions are met, this access method might used for a single XML index, even if multiple qualified indexes exist. |
| DOCID ANDing | DI | Retrieval of rows for two predicates that include XPath expressions, when the predicates are connected by AND. Db2 creates a DOCID list for each predicate and forms the intersection of them. |
| DOCID ORing | DU | Retrieval of rows for two predicates that include XPath expressions, when the predicates are connected by OR. Db2 creates a DOCID list for each predicate and forms the union of them. |
- Truly exact match
- An exact match, meaning that both XPath expressions are identical.
This method is used only for the XML index with the SQL data type
VARCHAR. For example: XPath expression in
XMLEXISTS: /a/b/c, and XPath expression in the XML index: /a/b/c. - Exact match but the ending part of the XPath expression in XMLEXISTS is in a predicate
- Used only when the XPath predicate is a general comparison with
operator =, <, <=, >, or >=. The data type of the operands
in the predicate must match to the index data type. For example, XPath
expression in
XMLEXISTS: /a[b/@c > 123], and XPath expression in the XML index: /a/b/@c. - Partial exact match with residual steps
- Used to evaluate the XPath expression which has more steps than
the first two methods. These extra steps in the XPath expression of
XMLEXISTS are called 'residual steps'. For example: XPath expression
in
XMLEXISTS: /a/b[c > “xyz”]//d[e=8], and XPath expression in the XML index: /a/b/c. - Partial match for index filtering
- The methods above have segments in the XPath expressions of XMLEXISTS
that match “well” with the XPath expression of an index. This method
handles the cases where the XPath expression in XMLEXISTS does not
match so well with the XPath expression of an index. For example:
XPath expression in
XMLEXISTS: /a[b/c = 5]/d, and XPath expression in the XML index: //c. - Partial exact match with ANDing and ORing on DOCID lists
- The XPath expression might be decomposed into multiple XPath
segments which are ANDed or ORed together to produce a super set of
the final result. The methods above apply to each of the decomposed
XPath segments to determine whether the XML index can be used to evaluate
the XPath segment. For example: XPath expression in
XMLEXISTS: /a/b[c = “xyz” and d > “abc”], and XPath expressions in the XML indexes: /a/b/c, and /a/b/d. - Partial match for filtering combined with ANDing and ORing on DOCID lists
- Partial match for filtering can be combined with partial exact
match with ANDing and ORing on DOCID lists. For example: XPath expression
in
XMLEXISTS: /a/b[@c = 5 or d > “a” ]/e, and XPath expressions in the XML indexes: //@c, and /a/b/d.
