List prefetch is used for access to a moderate number of rows when access to the table is by non-clustering or clustering index when the data is disorganized. List prefetch is less useful for queries that process large quantities of data or small amounts of data.
The preferred method of table access is by using a clustered index. When an index is defined as clustered, DB2 tries to keep the data in the table in the same sequence as the key values in the clustering index. When the table is accessed using the clustering index and the data in the table is organized, the access to the data in the table typically is sequential and predictable. If accessing the data in a table using a non-clustering index or the clustering index with a low cluster ratio, the access to the data can be random and unpredictable. In addition, the same data pages could be read multiple times.
When DB2 detects that a non-clustering index or a clustering index that has a low cluster ratio is used to access a table, DB2 may choose the list prefetch index access method instead.
Note: List prefetch is indicated in the PLAN_TABLE with a value of L in the PREFETCH column.
List prefetch accesses the index using a matching index scan of one or more indexes and collects the RIDs into the RID pool. RID pool is a common area of memory. The RIDs are sorted in ascending order by the page number, and the pages in the table space are retrieved in a sequential or a skip sequential way.
If DB2 determines that the RIDs to be processed will take more than 50 percent of the RID pool when the query is executed, list prefetch is not chosen as the access path. If DB2 determines that more than 25 percent of the rows of the table must be accessed, list prefetch can terminate during the execution. These situations are known as RDS failures. During RDS failures, the access path changes to a table space scan.
|
Copyright © 2013 CA Technologies.
All rights reserved.
|
|