List prefetch (PREFETCH='L' or 'U')

List prefetch reads a set of data pages determined by a list of record identifiers (RIDs) taken from an index.

Begin program-specific programming interface information.

List prefetch access paths are ideally suited for queries where the qualified rows, as determined in index key sequence, are not sequential, are skip-sequential but sparse, or when the value of the DATAREPEATFACTORF statistic is large.

With list prefetch the fetched data pages do not need to be contiguous. The maximum number of pages that can be retrieved in a single list prefetch is 32 (64 for utilities). Start of change The value of the PREFETCH column is set to 'L' or 'U' when DB2® expects to use list prefetch. End of change

List prefetch can be used with either single or multiple index access.

When the optimizer chooses the list prefetch access path, DB2 uses the following process:

Retrieve the list of rows identifiers through a matching index scan on one or more index.
Sort the list of row identifiers in ascending order by page number.
Prefetch the pages in order, by using the sorted list of row identifiers.

List prefetch does not preserve the data ordering given by the index. Because the RIDs are sorted in page number order before data access, the data is not retrieved in order by any column. If the data must be ordered for an ORDER BY clause or any other reason, it requires an additional sort. DB2 sometimes uses list prefetch without sorting the RID list for performance reasons. When that happens, the value of the PREFETCH column is set to 'U'.

In a hybrid join, if the index is highly clustered, the page numbers might not be sorted before accessing the data.

List prefetch can be used with most matching predicates for an index scan.

When list prefetch is used

List prefetch is used for the following operations:

Typically with a single index that has a cluster ratio lower than 80%
Sometimes on indexes with a high cluster ratio, if the estimated amount of data to be accessed is too small to make sequential prefetch efficient, but large enough to require more than one regular read
Always to access data by multiple index access
Always to access data from the inner table during a hybrid join
Typically for updatable cursors when the index contains columns that might be updated.
When IN-list predicates are used through an in-memory table as matching predicates (ACCESSTYPE='IN').

DB2 uses the RID pool for list prefetch processing. The MAXRBLK subsystem parameter controls the maximum size of the RID pool. If a single list prefetch operation tries to use too much of the RID pool or attempts to read too many rows from the table, the access path might revert to a table space scan. Start of change However, you can specify that RID list processing continues in work files by setting the value of the MAXTEMPS_RID subsystem parameter. End of change

Advantages of list prefetch

List prefetch is most useful for skip-sequential access when a number of non-sequential data pages are accessed in sequential order, but with intervening pages that do not contain needed data. In such cases, dynamic prefetch reads all of the intervening pages, the number of asynchronous pages read exceeds the number of get page operations, and the buffer pool is not used efficiently. List prefetch offers the following specific advantages over dynamic prefetch:

List prefetch uses buffers very economically.
List prefetch is not sensitive to index cluster ratio and performs much better than dynamic prefetch when the data getpages are sparse.
Sorted list prefetch never uses two getpage operations for the same data page.
If several data pages need to be skipped, list prefetch minimizes the channel time, enabling faster I/O than dynamic prefetch if the control unit hit ration is high.
For some types of control units, list prefetch is faster than sequential I/O for skip-sequential access. You can check with your storage vendor to learn whether that is true for your type of control unit.

Disadvantages of list prefetch

However, when compared to dynamic prefetch, list prefetch also has certain disadvantages :

Dynamic prefetch outperforms list prefetch when row identifiers are very dense, such as range scans when the cluster ratio is high.
For some types of control units, list prefetch is slower than sequential I/O for skip-sequential access. You can check with your storage vendor to learn whether that is true for your type of control unit.
The SEQCACH installation parameter does not apply to list prefetch. Therefore, data might not be streamed into the control unit cache from the disk on some control units models. This lack of streaming might or might not hurt performance because some types of control units might prefer to target the prefetch to a smaller subset of pages. You can check with your storage vendor to find out how your type of control unit performs in these situations.
For queries that use an ORDER BY or GROUP BY clause that uses an index key column, list prefetch always requires a be sort of the result set, whereas dynamic prefetch does not always require a sort. The cost of the sort depends on of the size of the result set, rather than the number of data pages read.
If an application prematurely closes a cursor before fetching the entire result set, the time that list prefetch used to process the index and create the sorted RID list is wasted.