Example of DOCID ORing access (ACCESSTYPE='DU')

Two XMLEXISTS predicates that are connected with OR might be eligible for DOCID ORing access.

Begin program-specific programming interface information.

Both predicates must have an eligible XML index.

The following query retrieves all XML documents from the INFO column of the CUSTOMER table for a customer whose zip code is 95141 or whose street name, when converted to uppercase, is BAILEY AVE.

SELECT INFO FROM CUSTOMER
 WHERE XMLEXISTS('$x/customerinfo/address[@zip=”95141” 
 or fn:upper-case(street) = "BAILEY AVE"]'
 PASSING CUSTOMER.INFO as "x")

The following index matches the first predicate.

CREATE INDEX CUST_ZIP_STR ON CUSTOMER(INFO)
	GENERATE KEY USING XMLPATTERN '/customerinfo/address/@zip' 
  AS VARCHAR(10)

The following index matches the second predicate.

CREATE INDEX CUST_STREET_UPPER ON CUSTOMER(INFO)
 GENERATE KEY USING XMLPATTERN '/customerinfo/address/street/fn:upper-case(.)'
 AS VARCHAR(50)

Db2 uses DOCID list access (DX) to get the DOCID lists for the individual predicates, and then uses DOCID ORing access (DI) to get the union of DOCID lists after an index scan of CUST_ZIP_STR and CUST_STREET_UPPER.

An excerpt from the The PLAN_TABLE output for the query looks like this:

Table 1. Example of columns of PLAN_TABLE for DOCID ORing access
PLAN NO	ACCESS TYPE	MATCH COLS	ACCESS NAME	MIXOP SEQ
1	M	0		0
1	DX	1	CUST_ZIP_STR	1
1	DX	1	CUST_STREET_UPPER	2
1	DU	0		3

End program-specific programming interface information.