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

arises. For now we must simply be aware of the relative costs involved in
accessing index or table rows from pages that may or may not be stored in
the buffer pools.

Reads from the DBMS Buffer Pool

If an index or table page is found in the buffer pool, the only cost involved is
that of the processing of the index or table rows. This is highly dependent on
whether the row is rejected or accepted by the DBMS, the former incurring very
little processing, the latter incurring much more as we will see in due course.

Random I/O from Disk Drives

Figure 2.2 shows the enormous cost involved in having to wait for a page to be
read into the buffer pool from a disk drive.
Again, we must remember that a page will contain several rows; we may be
interested in all of these rows, just a few of them, or even only a single row—the
cost will be the same, roughly 10 ms. If the disk drives are heavily used, this
figure might be considerably increased as a result of having to wait for the disk
to become available. In computing terms, 10 ms is an eternity, which is why we
will be so interested in this activity throughout this topic.
It isn't really necessary to understand how this 10 ms is derived, but for
those readers who like to understand where numbers such as this come from,
Figure 2.3 breaks it down into its constituent components. From this we can see
that we are assuming the disk would actually be busy for about 6 out of the
10 ms. The transfer time of roughly 1 ms refers to the movement of the page
from the disk server cache into the database buffer pool. The other 3 ms is an
estimate of the queuing time that might arise, based on disk activity of, say, 50
reads per second. These sort of figures would equally apply to directly attached
drives; all the figures will, of course, vary somewhat, but we simply need to keep
in mind a rough, but not unreasonable, figure of 10 ms.

Database
buffer pool

Move one page
(4K or 8K)

Rotating
drive

Best estimate: 10 ms

Service time: small range with current drives
Queuing time: large variation
Figure 2.2 Random I/O from disk drive—1.