Multiple index scans
Sometimes Db2® chooses to process a query using multiple index scans, building a composite RID list by ANDing and ORing RIDs which match each predicate, until one final RID list is created. The list is then used to access the data table pages using list prefetch.
For multiple index scans, several points are worth noting. The MIXSEQ (multiple index operational sequence) number identifies each step within a PLANNO (plan number) that is taken during the multiple index access. Each index is a matching scan, done as index only. The same index can appear several times in the sequence. If the predicates are linked by AND, the intersection of the two RID lists is used because a row satisfying the predicate clause must exist in both lists. If the predicates are joined by OR, the union of the two RID lists is used because a row satisfying one of the predicates could appear either on RID list A or RID list B. SQL PA explains each of the steps in this process, including the final data access using List Prefetch.
The following example shows a sample report for a multiple index scan. Three different indexes were used for the Multiple Index access query in the sample. In step 1 the CIN index was accessed and, in step 2, the R1N index. These are intersected in step 3 (MI) to create a composite list made up of qualifying RIDs. Then the R2N index is accessed in step 4, and its RIDs are unioned (MU) with the composite RID list in step 5. Finally, data was accessed using list prefetch. Db2 Explain identifies this action as step 0 (MIXSEQ = 0), but it is performed last, not first.
EXPLAIN PLAN SET QUERYNO = 100000006 FOR
SELECT * FROM TDT690.L1000
WHERE CIKEY BETWEEN 1001 AND 1100 AND RIKEY1 < 10000 OR
RIKEY2 BETWEEN 52 AND 1500
QUERYNO: 100000006 QBLOCKNO: 1 PLANNO: 1 MIXOPSEQ: 0
PROCESS ->
A MULTIPLE INDEX OPERATION HAS BEEN REQUESTED TO ACCESS THIS TABLE:
--------------------------------------------------------------------------------
QUERYNO: 100000006 QBLOCKNO: 1 PLANNO: 1 MIXOPSEQ: 1
PROCESS ->
CLUSTER MATCH IX SCAN
---------------------
IX CREATOR: TDT690
INDEX NAME: L1000CIN
VERS: 0 KEY LEN: 6 PADDED: - C-ED: Y C-ING: Y CLUSRATIO: 99.9995
FULLKEY CARD: 99340 FIRSTKEY CARD: 99340
TYPE: 2 NLEAF PAGES: 412 NLEVELS: 3 UNIQUE: D DUPLICATE OK
1 OF 1 COLUMNS ARE MATCHED CLOSE: Y LOCK MODE: IS BPOOL: BP10
KEY COLUMN NAME ORDER TYPE DIST LEN NULL COLCARD DIST#
------------------------------------------------------------------------
1 CIKEY A DECIMAL N 9 Y 99340 10
THIS IS THE CLUSTERING ("INSERT & LOAD ORDER") INDEX FOR THIS TABLE
THIS IS AN "INDEX ONLY" ACCESS: NO DATA PAGES ARE READ FROM THE TABLE
--------------------------------------------------------------------------------
QUERYNO: 100000006 QBLOCKNO: 1 PLANNO: 1 MIXOPSEQ: 2
PROCESS ->
RANDOM MATCH IX SCAN
--------------------
IX CREATOR: TDT690
INDEX NAME: L1000R1N
VERS: 0 KEY LEN: 6 PADDED: - C-ED: N C-ING: N CLUSRATIO: 11.6627
FULLKEY CARD: 5954 FIRSTKEY CARD: 5954
TYPE: 2 NLEAF PAGES: 155 NLEVELS: 2 UNIQUE: D DUPLICATE OK
1 OF 1 COLUMNS ARE MATCHED CLOSE: Y LOCK MODE: IS BPOOL: BP10
KEY COLUMN NAME ORDER TYPE DIST LEN NULL COLCARD DIST#
------------------------------------------------------------------------
1 RIKEY1 A DECIMAL N 9 Y 5954 10
THIS IS AN "INDEX ONLY" ACCESS: NO DATA PAGES ARE READ FROM THE TABLE
SEQUENTIAL PREFETCH WILL BE EMPLOYED DURING THIS INDEX ONLY ACCESS
--------------------------------------------------------------------------------
STEP 3. MULTIPLE INDEX INTERSECTION WAS PERFORMED (MI).
--------------------------------------------------------------------------------
QUERYNO: 100000006 QBLOCKNO: 1 PLANNO: 1 MIXOPSEQ: 4
PROCESS ->
RANDOM MATCH IX SCAN
--------------------
IX CREATOR: TDT690
INDEX NAME: L1000P2N
VERS: 0 KEY LEN: 6 PADDED: N C-ED: N C-ING: N CLUSRATIO: 70.0010
FULLKEY CARD: 100000 FIRSTKEY CARD: 100000
TYPE: 2 NLEAF PAGES: 359 NLEVELS: 3 UNIQUE: U DECLARE UNIQ
1 OF 1 COLUMNS ARE MATCHED CLOSE: Y LOCK MODE: BPOOL: BP10
+------------------------------------------------------------------+
|ANL6052I *** NOTE: |
|This index is specified as "Not Padded", allowing storage of a |
|varying length index key. Padded indexes use blanks to fill out |
|their fixed length keys and are not eligible for Index Only scan. |
|"Not Padded" indexes do not blank fill CHAR and VARCHAR columns, |
|allowing greater flexibility and better use of storage, packing |
|more entries into a single Leaf page. Index Only access allowed. |
+------------------------------------------------------------------+
KEY COLUMN NAME ORDER TYPE DIST LEN NULL COLCARD DIST#
------------------------------------------------------------------------
1 RIKEY2 A DECIMAL N 9 Y 100000 0
THIS IS AN "INDEX ONLY" ACCESS: NO DATA PAGES ARE READ FROM THE TABLE
--------------------------------------------------------------------------------
STEP 5. MULTIPLE INDEX UNION WAS PERFORMED (MU).
--------------------------------------------------------------------------------
MULTIPLE INDEX OPERATIONS ARE NOW COMPLETE: DATA ACCESS PERFORMED.
QUERYNO: 100000006 QBLOCKNO: 1 PLANNO: 1 MIXOPSEQ: 5
PROCESS ->
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
* QUERY 100000006 WILL REQUIRE 11.37197 SECONDS OF ELAPSED TIME *
* DURING WHICH 1.30339 SECONDS OF CPU TIME WILL BE CONSUMED AND *
* A TOTAL OF 11 PHYSICAL I/O REQUESTS WILL BE ISSUED TO DISK *
* QUNITS 39 ESTIMATED PROCESSING COST $ 8.2090 DOLLARS *
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*