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

4

Sort pointers

103

5

1

101

2

103

3

109

6

109

Read-ahead

101

7

Read-ahead

Index Table

Figure 2.7 DB2 List Prefetch.

DataBlockPrefetching

This feature is used by Oracle, again when the table rows being accessed are
not in the same sequence as the index rows. In this case, however, as shown in
Figure 2.8, the pointers are collected from the index slice and multiple random
I/Os are started to read the table rows in parallel. If the table rows represented
by steps 4, 5, and 6 reside on three different drives, all three random I/Os will
be performed in parallel. As with list prefetch, we could use Figures 2.6 and
2.8 to contrast an access path that does not use data block prefetching with one
that does.
Before we leave assisted random reads, it might be worth considering the
order in which a result set is obtained. An index could provide the correct
sequence automatically, whereas the above facilities could destroy this sequence
before the table rows were accessed, thereby requiring a sort.

Comment

Throughout this topic, we will refer to three types of read I/O operations: syn-
chronous, sequential, and assisted random reads; in order to make the estimation
process usable, initially only the first two types will be addressed, but Chapter 15
will discuss assisted random read estimation in some detail.
Note that SQL Server uses the term Index Read-Ahead and Oracle uses the
term Index Skip Scan . The former refers to the reading-ahead of the next leaf
pages following leaf page splits, while the latter refers to the reading of several
index slices instead of doing a full index scan.