Deriving the Ideal Index for a SELECT - Relational Database Index Design and the Optimizers - page 48

BASIC ASSUMPTIONS FOR DISK AND CPU TIMES

We have already discussed disk I/Os in considerable detail in Chapter 2; from that
information, we will now state some basic assumptions that we will use through-
out this topic. More information about CPU times will be found in Chapter 15.

Basic Assumptions for Disk and CPU Times
I/O Time
Random Read 10 ms (4K or 8K page)
Sequential Read 40 MB/s
CPU Time for Sequential Scan
Examine a Row 5 µ s

FETCH 100 µ s

INADEQUATE INDEX

The only reasonable access path alternatives for the following simple query (SQL
4.1 and Figure 4.1) are:
1. Index scan with (LNAME, FNAME)
2. Full table scan

SQL 4.1

DECLARE CURSOR41 CURSOR FOR
SELECT CNO, FNAME

FROM CUST

WHERE LNAME = :LNAME

AND
CITY = :CITY

ORDER BY FNAME

FF = 1%

P,C

LNAME,
FNAME

CNO

100 MB

CUST

600 MB

1,000,000 rows
150,000 pages

Figure 4.1 Full table scan or an
inappropriate index?