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

103

5

Read-ahead

1

101

2

103

3

109

109

6

Read-ahead

101

4

Index Table

Figure 2.8 Oracle data block prefetching.

Assisted Sequential Reads

When a large table is to be scanned, the optimizer may decide to activate par-
allelism; for instance, it may split a cursor into several range-predicate cursors,
each of which would scan one slice. When several processors and disk drives
are available, the elapsed time will be reduced accordingly. Again we will put
this to one side until we come to Chapter 15. Please note that the term assisted
sequential reads is again not one that is used by any of the DBMSs.

Synchronous and Asynchronous I/Os

Having discussed these different access techniques, it will be appropriate now to
ensure we fully appreciate one final consideration, synchronous and asynchronous
I/Os as shown in Figure 2.9.
The term synchronous I/O infers that while the I/O is taking place, the
DBMS is not able to continue any further; it is forced to wait until the I/O has
completed. With a synchronous read, for example, we have to identify the row
required (shown as “C” to represent the first portion of CPU time in the figure),
access the page and process the row (shown as the second portion of CPU time),
each stage waiting until the previous stage completes.
Asynchronous reads on the other hand are being performed in advance while
a previous set of pages are being processed; there may be a considerable overlap
between the processing and I/O time; ideally the asynchronous I/O will complete