Examples of DOCID single index access (ACCESSTYPE='DX' only)

If certain conditions are met, Db2 might select an access path that uses a single XML index.

Begin program-specific programming interface information.

Db2 might choose an access path that uses a single XML index under the following conditions:

The XPath predicate is an equality predicate.
The right side of the predicate is a literal value node.
The XML index is a unique index because either of the following cases is true:
- The UNIQUE clause was specified when the index was created.
- Statistics are available for the index and the FIRSTKEYCARDF and FULLKEYCARDF columns of the SYSIBM.SYSINDEXES catalog table contain the same value.

Examples

Two XML indexes created as UNIQUE and an XMLEXISTS predicate that contains two equality XPath predicates

Assume that the following XML indexes exists:

/* Index DDL */
CREATE UNIQUE INDEX IDX_KEYENTRY ON T1(REF_CKEY)                   
 GENERATE KEY USING   XMLPATTERN                                   
 '/WorkloadData/ReferenceKey/keyEntry'  AS SQL VARCHAR(20);        

CREATE INDEX IDX_NAME ON T1(REF_CKEY)                   
 GENERATE KEY USING   XMLPATTERN                                   
 '/WorkloadData/ReferenceKey/@name'  AS SQL VARCHAR(20);

Db2 might choose access through a single XML index for the following query:

SELECT ID                                                             
FROM T1                                                               
WHERE                                                                 
   XMLEXISTS('$xmlCol/WorkloadData/ReferenceKey
           [keyEntry[.="800187581" and @name="CLMSSN"]]'                  
   PASSING PT16SIG1.wklitm.ref_ckey as "xmlCol" )

If the access through a single index is chosen, a PLAN_TABLE row for the query might contain the following values:

QUERYNO	TABLENAME	ACCESSTYPE	ACCESSNAME
100	T1	DX	IDX_KEYENTRY

Two XML indexes created as UNIQUE and two XMLEXISTS predicates that contain equality XPath predicates

Assume that the same indexes from the previous example exist. Db2 might choose access through a single index for the following query:

SELECT ID                                                             
FROM T1                                                               
WHERE                                                                 
   XMLEXISTS('$xmlCol/WorkloadData/ReferenceKey
           [keyEntry="800187581"]'                  
           PASSING PT16SIG1.wklitm.ref_ckey as "xmlCol" )  and
   XMLEXISTS('$xmlCol/WorkloadData/ReferenceKey
           [keyEntry/@name="CLMSSN"]'                  
           PASSING PT16SIG1.wklitm.ref_ckey as "xmlCol" )

If Db2 chooses access through the single index, a PLAN_TABLE row for the query might contain the following values:

QUERYNO	TABLENAME	ACCESSTYPE	ACCESSNAME
200	T1	DX	IDX_KEYENTRY

XML indexes are not created as UNIQUE, but the values of the FIRSTKEYCARDF and FULLKEYCARDF columns for the index in SYSIBM.SYSINDEXES are equal.

Assume that the following indexes exist:

CREATE INDEX IDX_KEYENTRY ON T1(REF_CKEY)                   
 GENERATE KEY USING   XMLPATTERN                                   
 '/WorkloadData/ReferenceKey/keyEntry'  AS SQL VARCHAR(20);        

CREATE INDEX IDX_NAME ON T1(REF_CKEY)                   
 GENERATE KEY USING   XMLPATTERN                                   
 '/WorkloadData/ReferenceKey/@name'  AS SQL VARCHAR(20);

If statistics have been collected for the XML index, and the values of the FIRSTKEYCARDF and FULLKEYCARDF columns for the index in SYSIBM.SYSINDEXES are equal, Db2 might choose access through a single index for the following query:

/*Query*/
SELECT ID                                                             
FROM T1                                                               
WHERE                                                                 
   XMLEXISTS('$xmlCol/WorkloadData/ReferenceKey
           [keyEntry[.="800187581" and @name="CLMSSN"]]'                  
   PASSING PT16SIG1.wklitm.ref_ckey as "xmlCol" )

For XML indexes, the value of FIRSTKEYCARDF column in SYSIBM.SYSINDEXES represents the number of unique values in the XML index. The value of the FULLKEYCARDF column represents the total number of index entries in the XML index. IF the two values are equal, it is implied that only a single XML document qualifies for the equality predicate.

If Db2 chooses an access path that uses the single XML index, a PLAN_TABLE row might contain the following values for the query:

QUERYNO	TABLENAME	ACCESSTYPE	ACCESSNAME
300	T1	DX	IDX_KEYENTRY

End program-specific programming interface information.