Multiple index access (ACCESSTYPE='M', 'MX', 'MI', 'MU', 'DX', 'DI', or 'DU')

Multiple index access uses more than one index to access a table.

Begin program-specific programming interface information.

Multiple index access is a good access path when:

No single index provides efficient access.
A combination of index accesses provides efficient access.

RID lists are constructed for each of the indexes involved. The unions or intersections of the RID lists produce a final list of qualified RIDs that is used to retrieve the result rows, using list prefetch. You can consider multiple index access as an extension to list prefetch with more complex RID retrieval operations in its first phase. The complex operators are union and intersection.

Db2 does not use multiple-index access for IN-list predicates.

Db2 chooses multiple index access for the following query:

SELECT * FROM EMP
   WHERE (AGE = 34) OR
         (AGE = 40 AND JOB = 'MANAGER');

For this query:

EMP is a table with columns EMPNO, EMPNAME, DEPT, JOB, AGE, and SAL.
EMPX1 is an index on EMP with key column AGE.
EMPX2 is an index on EMP with key column JOB.

The plan table contains a sequence of rows describing the access. For this query, ACCESSTYPE uses the following values:

Value: Meaning
M: Start of multiple index access processing
MX: Indexes are to be scanned for later union or intersection
MI: An intersection (AND) is performed
MU: A union (OR) is performed

The following steps relate to the previous query and the values shown for the plan table in the following table:

Index EMPX1, with matching predicate AGE = 40, provides a set of candidates for the result of the query. The value of MIXOPSEQ is 1.
Index EMPX2, with matching predicate JOB = 'MANAGER', also provides a set of candidates for the result of the query. The value of MIXOPSEQ is 2.
The first intersection (AND) is done, and the value of MIXOPSEQ is 3. This MI removes the two previous candidate lists (produced by MIXOPSEQs 2 and 3) by intersecting them to form an intermediate candidate list, IR1, which is not shown in PLAN_TABLE.
Index EMPX1, with matching predicate AGE = 34, also provides a set of candidates for the result of the query. The value of MIXOPSEQ is 4.
The last step, where the value MIXOPSEQ is 5, is a union (OR) of the two remaining candidate lists, which are IR1 and the candidate list produced by MIXOPSEQ 1. This final union gives the result for the query.

Table 1. Plan table output for a query that uses multiple indexes. Depending on the filter factors of the predicates, the access steps can appear in a different order.
PLAN- NO	TNAME	ACCESS- TYPE	MATCH- COLS	ACCESS- NAME	PREFETCH	MIXOP- SEQ
1	EMP	M	0		L	0
1	EMP	MX	1	EMPX1		1
1	EMP	MX	1	EMPX2		2
1	EMP	MI	0			3
1	EMP	MX	1	EMPX1		4
1	EMP	MU	0			5

The multiple index steps are arranged in an order that uses RID pool storage most efficiently and for the least amount of time.

Execution order for multiple index access

A set of rows in the plan table contain information about the multiple index access. The rows are numbered in column MIXOPSEQ in the order of execution of steps in the multiple index access. (If you retrieve the rows in order by MIXOPSEQ, the result is similar to postfix arithmetic notation.)

Both of the following examples have these indexes: IX1 on T(C1) and IX2 on T(C2).

Example: index access order

Suppose that you issue the following SELECT statement:

SELECT * FROM T
  WHERE C1 = 1 AND C2 = 1;

Db2 processes the query by performing the following steps:

Db2 retrieves all the qualifying record identifiers (RIDs) where C1=1, by using index IX1.
Db2 retrieves all the qualifying RIDs where C2=1, by using index IX2. The intersection of these lists is the final set of RIDs.
Db2 accesses the data pages that are needed to retrieve the qualified rows by using the final RID list.

The plan table for this example is shown in the following table.

Table 2. PLAN_TABLE output for example with intersection (AND) operator
TNAME	ACCESS- TYPE	MATCH- COLS	ACCESS- NAME	INDEX- ONLY	PREFETCH	MIXOP- SEQ
T	M	0		N	L	0
T	MX	1	IX1	Y		1
T	MX	1	IX2	Y		2
T	MI	0		N		3

Example: multiple access to the same index

Suppose that you issue the following SELECT statement:

SELECT * FROM T
  WHERE C1 BETWEEN 100 AND 199 OR
        C1 BETWEEN 500 AND 599;

In this case, the same index can be used more than once in a multiple index access because more than one predicate could be matching. Db2 processes the query by performing the following steps:

Db2 retrieves all RIDs where C1 is in the range 100–199, using index IX1.
Db2 retrieves all RIDs where C1 is in the range 500–599, again using IX1. The union of those lists is the final set of RIDs.
Db2 retrieves the qualified rows by using the final RID list.

The plan table for this example is shown in the following table.

Table 3. PLAN_TABLE output for example with union (OR) operator
TNAME	ACCESS- TYPE	MATCH- COLS	ACCESS- NAME	INDEX- ONLY	PREFETCH	MIXOP- SEQ
T	M	0		N	L	0
T	MX	1	IX1	Y		1
T	MX	1	IX1	Y		2
T	MU	0		N		3

Example: multiple index access for XML data

Suppose that you issue the following SELECT statement that uses indexes to evaluate the XMLEXISTS predicates.

SELECT * FROM T
		WHERE (C1 = 1 OR C2 = 1) AND
			XMLEXISTS('/a/b[c = 1]' PASSING XML_COL1) AND
			XMLEXISTS('/a/b[(e = 2 or f[g = 3]) and h/i[j = 4] ]'

		PASSING XML_COL2);

The following statement shows the indexes defined on T:

IX1: C1
IX2: C2
VIX1: /a/b/c
VIX2: /a/b/e
VIX3: /a/b/f/g
VIX4: /a/b/h/i/j
DOCID index on T: DIX1

The XPath expression in the second XMLEXISTS predicate is decomposed into multiple XPath segments which are combined by AND and OR operations. Db2 matches each XPath segment to an XML index. The matching information between the predicates and indexes are as follows:

Table 4. Matching between predicates and indexes for the XMLEXISTS example
Predicate	Matching Index	XML index
C1 = 1	IX1	N
C2 = 1	IX2	N
XMLEXISTS 1: /a/b[c =1]	VIX1	Y
XMLEXISTS 2: /a/b[e = 2]	VIX2	Y
XMLEXISTS 2: /a/b/f[g = 3]	VIX3	Y
XMLEXISTS2: /a/b/h/i[j = 4]	VIX4	Y

Db2 uses the above indexes to access the table T and processes the query by performing the following steps:

Db2 retrieves all the qualifying record identifiers (RIDs) where C1=1, by using index IX1.
Db2 retrieves all the qualifying RIDs where C2 = 1, by using index IX2.
The union of the RID lists from step 1 and 2 is the final set of qualifying RIDs where C1 = 1 OR C2 = 1.
Db2 retrieves all the qualifying DOCIDs where /a/b[c =1], by using index VIX1.
Db2 retrieves all the qualifying RIDs of the DOCID list from step 4, by using index DIX1.
The intersection of the RID lists from step 3 and 5 is the final set of qualifying RIDs where (C1 = 1 OR C2 = 1) AND XPath expression /a/b[c =1].
Db2 retrieves all the qualifying DOCIDs where /a/b[e = 2], by using index VIX2.
Db2 retrieves all the qualifying DOCIDs where /a/b/f[g = 3], by using index VIX3.
The union of the DOCID lists from step 7 and 8 is the final set of qualifying DOCIDs where XPath segment /a/b[e = 2] OR XPath segment /a/b/f[g = 3].
Db2 retrieves all the qualifying DOCIDs where /a/b/h/i[j = 4], by using index VIX4.
The intersection of the DOCID lists from step 9 and 10 is the final set of qualifying DOCIDs where (XPath segment /a/b[e = 2] OR XPath segment /a/b/f[g = 3]) AND XPath segment / a/b/h/i[j = 4].
Db2 retrieves all the qualifying RIDs of the DOCID list from step 11, by using index DIX1.
The intersection of the RID lists from step 6 and 12 is the final set of qualifying RIDs where (C1 = 1 OR C2 = 1) AND XPath expression /a/b[c =1] AND ((XPath segment /a/b[e = 2] OR XPath segment /a/b/f[g = 3]) AND XPath segment /a/b/h/i[j = 4]). Db2 accesses the data pages that are needed to retrieve the qualified rows by using the final RID list.

The plan table for this example is shown in the following table:

Table 5. The PLAN_TABLE output for the XMLEXISTS predicate example
QUERY NO	Q BLOCK NO	PLAN NO	T NAME	TAB NO	ACCT TYPE	MATCH COLS	ACC NAME	INDEX- ONLY	MIX OP SEQ
1	1	1	T	1	M	0		N	0
1	1	1	T	1	MX	1	IX1	Y	1
1	1	1	T	1	MX	1	IX2	Y	2
1	1	1	T	1	MU	0		N	3
1	1	1	T	1	DX	1	VIX1	Y	4
1	1	1	T	1	MX	0	DIX1	Y	5
1	1	1	T	1	MI	0		N	6
1	1	1	T	1	DX	1	VIX2	Y	7
1	1	1	T	1	DX	1	VIX3	Y	8
1	1	1	T	1	DU	0		N	9
1	1	1	T	1	DX	1	VIX4	Y	10
1	1	1	T	1	DI	0		N	11
1	1	1	T	1	MX	1	DIX1	Y	12
1	1	1	T	1	MI	0		N	13

End program-specific programming interface information.