Table and Index Organization - Relational Database Index Design and the Optimizers - page 17

AutomaticSkip-Sequential

By definition, an access pattern will be skip-sequential if a set of noncontiguous
rows are scanned in one direction. The I/O time per row will thus be automatically
shorter than with random access; the shorter the skips, the greater the benefit.
This would occur, for example, when table rows are being read via a clustering
index and index screening takes place, as we will see in due course. This benefit
can be enhanced in two ways:

1. The disk server may notice that access to a drive is taking place sequen-
tially, or almost sequentially, and starts to read several pages ahead.
2. The DBMS may notice that a SELECT statement is accessing the pages
of an index or table sequentially, or almost sequentially, and starts to read
several pages ahead; this is called dynamic prefetch in DB2 for z/OS.

ListPrefetch

In the previous example, this benefit was achieved simply as a result of the table
and index rows being in the same sequence. DB2 for z/OS is in fact able to
create skip-sequential access even when this is not the case; to do this, it has
to access all the qualifying index rows and sort the pointers into table page
sequence before accessing the table rows. Figures 2.6 and 2.7 contrast an access
path that does not use list prefetch with one that does, the numbers indicating
the sequence of events.

Page

103

4

101

1

103
109

3

5

109

6

2

101

Index Table

Figure 2.6 Traditional index scan.