Factors Affecting the Index Design Process - Relational Database Index Design and the Optimizers - page 86

Chapter 6

Factors Affecting the Index
Design Process

ž Important factors that affect the index design process considered in
Chapters 4 and 5
ž Verification of the basic estimates
ž Multiple thin slices
ž DBMS specifics
ž Difficult predicates for optimizers
ž Boolean predicates
ž Filter factor problems and pitfalls

I/O TIME ESTIMATE VERIFICATION

In Chapter 4 we introduced the following I/O times:

Random Read 10 ms (4K or 8K page)
Sequential Read 40 MB/s
These numbers assume current hardware with a reasonable load. Some instal-
lations may be slower or overloaded, and so it might be useful to perform the
following checks. To do this, a table can be created with a million rows having an
average length of about 400 bytes. The assumed sequential I/O time per 400-byte
row is then 400 bytes / 40 MB/s = 0 . 01 ms.
The time taken to perform the following scans is then determined (the figures
in parentheses are the predictions based on our estimates as used in Chapter 4):
ž A full table scan with a singleton SELECT that rejects all rows (1 ×
10 ms + 1 , 000 , 000 × 0 . 01 ms = 10 s)
ž An index scan with 1000 FETCH calls, using an index such as (LNAME,
FNAME), that causes an index slice scan of 1000 rows and 1000 syn-
chronous reads to the table (1000

× . + × = +

0 01 ms 1000 10 ms 10 ms

10 s = 10 s)