List prefetching

List prefetching (or list sequential prefetching) is a way to access data pages efficiently, even when those pages are not contiguous.

List prefetching can be used in conjunction with either single or multiple index access.

If the optimizer uses an index to access rows, it can defer reading the data pages until all of the row identifiers (RIDs) have been obtained from the index. For example, the optimizer could perform an index scan to determine the rows and data pages to retrieve.

   INDEX IX1:  NAME    ASC,
               DEPT    ASC,
               MGR     DESC,
               SALARY  DESC,
               YEARS   ASC

And then use the following search criteria:

   WHERE NAME BETWEEN 'A' and 'I'

If the data is not clustered according to this index, list prefetching includes a step that sorts the list of RIDs that were obtained from the index scan.