Random matching index scans
The random matching index scan is like the cluster matching index scan with two major differences.
The differences include the following characteristics:
- The rows in the data table are not clustered together in the order of the index, but rather scattered randomly throughout the pages of the table.
- The list prefetch is used to retrieve the data pages. The RIDs are sorted into the proper order of appearance (table page order), so that the list prefetch I/O is sequential, if not contiguous, in nature. An example of the information for a random matching index scan is shown in the following information.
Db2 SQL Performance Analyzer provides the same basic information for a random index as it does for a clustered index and, if there is table access, the form and structure of the table is also shown.
EXPLAIN PLAN SET QUERYNO = 100000004 FOR
SELECT * FROM L1000
WHERE RIKEY2 = 38 ;
QUERYNO: 100000004 QBLOCKNO: 1 PLANNO: 1 MIXOPSEQ: 0
PROCESS ->
RANDOM MATCH IX SCAN
--------------------
IX CREATOR: TDT690
INDEX NAME: L1000R2N
VERS: 0 KEY LEN: 6 PADDED: - C-ED: N C-ING: N CLUSRATIO: 70.0010
FULLKEY CARD: 100000 FIRSTKEY CARD: 100000
TYPE: 2 NLEAF PAGES: 359 NLEVELS: 3 UNIQUE: U KEY COLS: 1
1 OF 1 COLUMNS ARE MATCHED CLOSE: Y LOCK MODE: IS BPOOL: BP10
KEY COLUMN NAME ORDER TYPE DIST LENGTH NULL COLCARD DIST#
----------------------------------------------------------------------
1 RIKEY2 A DECIMAL N 9 Y 100000 0
ALTERNATIVE INDEX
+++++++++++++++++
CREATOR: TDT690
IX NAME: L1000CIN
VERS: 0 KEY LEN: 6 PADDED: - C-ED: Y C-ING: Y CLUSRATIO: 99.9995
FULL KEY CARD: 99340 FIRST KEY CARD: 99340
TYPE: D NLEAF PAGES: 412 NLEVELS: 3 UNIQUE: D KEY COLS: 1
KEY ORDER COLCARD COLUMN NAME
----------------------------------------------------
1 A 99340 CIKEY
ALTERNATIVE INDEX
+++++++++++++++++
CREATOR: TDT690
IX 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 KEY COLS: 1
KEY ORDER COLCARD COLUMN NAME
--------------------------------------------------
1 A 5954 RIKEY1
MATCHING IX SCAN
----------------
CREATOR: TDT690
TABNAME: L1000
THIS INDEXED ACCESS IS APPLIED TO 32K SEGMENTED SPACE WITH: 3 TABLES
VERSION: 1 TABLE CONTAINS A TOTAL OF: 33335 4K PAGES
TABLE ROWS: 100003 COLUMNS: 50 REC LENGTH: 1008 BYTES
DSSIZE: 32 GB NUMBER OF MQTS: 12 LOG: Y AVG ROW LENGTH: 804 BYTES
TYPE: M LOCK SIZE: A TS LOCK MODE: IS LOCK PART: N CLOSE TABLE: Y
PAGES WITH ROWS: 99% PCT COMPRESSED: 0% MAX ROW: 255 BPOOL: BP32K9
ENCODE: E CCSIDS ARE SBCS: 4096 DBCS: 2048 MIXED: 7077 VOLATILE: Y
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
* QUERY 100000004 WILL REQUIRE 1.92242 SECONDS OF ELAPSED TIME *
* DURING WHICH 0.03342 SECONDS OF CPU TIME WILL BE CONSUMED AND *
* A TOTAL OF 4 PHYSICAL I/O REQUESTS WILL BE ISSUED TO DISK *
* QUNITS 1 ESTIMATED PROCESSING COST $ 0.0575 DOLLARS *
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*