Appendix A. DB2 access techniques 423
Example: A-1 Index screening predicate
SELECT COLa FROM TB1
WHERE COLb = 1
AND COLc > 0
AND COLd < 2;
The predicates can be applied to the index, but they are not all matching predicates. COLc
and COLd are index screening predicates. There is one matching column in the example,
which is COLb.
Multiple index access
Multiple index access uses more than one index to access a table. It is a good access path
when no single index provides efficient access and a combination of index accesses
provides efficient access. RID lists are constructed for each index involved. RIDs
qualifying in all indexes are sorted and used for data access using list prefetch.
DB2 checks whether the required entry is in the leaf page accessed by the previous call
and checks against the lowest an highest key of the leaf page. If the entry is found, DB2
can avoid the getpage and traversal of the index tree.
If the entry is not within the cached range, DB2 checks the parent nonleaf page’s lowest
and highest key. If the entry is found in the parent nonleaf range, DB2 has to perform a
getpage, but can avoid a full traversal of the index tree.
In case an entry is not found within the parent nonleaf page, DB2 starts an index probe
from the index root page. See also “Index look-aside” on page 426.
Direct row access
Allows DB2 to navigate directly to that row without using an index access or a table space
scan to find the row.
You can find more details about direct row access in “Data access using ROWID” on
A.2 Prefetch mechanisms
For table space scans, DB2 generally uses sequential prefetch. Sequential prefetch reads a
sequential set of pages into the buffer pool with one asynchronous I/O. Usually the maximum
number of pages is 32 for a base table.
Sometimes sequential prefetch is also used for index scans:
If index cluster ratio greater than 0.8
For index only, if the number of qualified leaf pages > 8
For index and data, if the number of qualified clustered data pages > 8
If DB2 is not able to choose sequential prefetch at bind time, it may still utilize it at run time:
DB2 determines further if data access is sequential or nearly sequential. This kind of
sequential detection is also known as
Data access is declared sequential if more than four out of the last eight pages are
page-sequential (not valid for DB2 internal referential integrity checks).
Tracking is continuous, allowing access to slip into and out of sequential data access.