Random nonmatching index scans
The random nonmatching index scan is seldom chosen as an access path by Db2®. It is not efficient to read the entire index leaf tree, and one page per row retrieved (the assumption of the optimizer).
However, certain built-in functions, left unqualified by further WHERE clause predicates, are processed best using a random nonmatching index scan.
It is not uncommon to see the COUNT all rows in the table function processed as a nonmatching index scan against the smallest index on the table that counts all the RIDs that are encountered. The random nonmatching index scan displays the complete set of index statistics and includes the table characteristics, if the query requires rows from the data table. The query in the example does not.
The following example uses the COUNT(*) clause:
EXPLAIN PLAN SET QUERYNO = 100000005 FOR
SELECT COUNT (*) FROM L1000
QUERYNO: 100000005 QBLOCKNO: 1 PLANNO: 1 MIXOPSEQ: 0
PROCESS ->
+------------------------------------------------------------------+
|ANL7003I *** GUIDELINE: |
|Close Yes was specified for the Tablespace and/or the index... |
|if these are little used this is OK. If high volume access then |
|consider Close No. Extremely relevant pages can be "page fixed" |
|in memory by highly referencing, using Hiperpools (Castout Y/N), |
|putting into a dedicated buffer pool large enough to hold all |
|pages, deploying data sharing with Group Buffer Pool Cache All |
|option, etc. each with associated costs. Close No also increases |
|chances that DBD will remain in EDM Pool for next execution. |
+------------------------------------------------------------------+
RANDOM NONMATCH 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 KEY COLS: 1
NONE 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
ALTERNATIVE INDEX
+++++++++++++++++
CREATOR: TDT690
IX 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 KEY COLS: 1
KEY ORDER COLCARD COLUMN NAME
--------------------------------------------------
1 A 99340 CIKEY
ALTERNATIVE INDEX
+++++++++++++++++
CREATOR: TDT690
IX 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
KEY ORDER COLCARD COLUMN NAME
--------------------------------------------------
1 A 100000 RIKEY2
+------------------------------------------------------------------+
|ANL6020I *** NOTE: |
|This index is presently designated as allowing "duplicate values".|
|Make sure this is a nonunique index that does not fall into one of|
|the following "unique" categories: explicitly unique, primary key,|
|non-primary RI parent key, unique where not null, unique column |
|constraint. Unique indexes have definite advantages whenever they |
|can be declared by the user, so be sure it contains duplicates. |
+------------------------------------------------------------------+
THIS IS AN "INDEX ONLY" ACCESS: NO DATA PAGES ARE READ FROM THE TABLE
SEQUENTIAL PREFETCH WILL BE EMPLOYED DURING THIS INDEX ONLY ACCESS
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
* QUERY 100000005 WILL REQUIRE 2.16076 SECONDS OF ELAPSED TIME *
* DURING WHICH 0.02317 SECONDS OF CPU TIME WILL BE CONSUMED AND *
* A TOTAL OF 7 PHYSICAL I/O REQUESTS WILL BE ISSUED TO DISK *
* QUNITS 2 ESTIMATED PROCESSING COST $ 0.0812 DOLLARS *
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*