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

are not the worst case because this is another example of the filter factor pitfall.
The three conditions for the filter factor pitfall are true for this Cursor with index
(LNAME, FNAME):

ž There is no sort in the access path.
ž The transaction responds as soon as the first screen is built.
ž All the predicate columns do not participate in defining the index slice to
be scanned—in other words, they are not all matching columns.

Therefore, it may take longer to create the message “No qualifying customers”
than the first screen of a large result set.
Let's consider the extreme case where the DBMS has to scan a maximum
index slice but produces an empty result set—a city in which we have no cus-
tomers with the most common last name; now the DBMS must still scan the
whole index slice and check a table row for each index row.

Index LNAME, FNAME TR = 1 TS = 1% × 1,000,000

Table CUST TR = 10,000 TS = 0

Fetch 0 × 0.1 ms

LRT Empty Result Set TR = 10,001 TS = 10,000

10,001 × 10 ms 10,000 × 0.01 ms
100 s + 100 ms + 0 ms = 100 s

Nearly 2 min instead of 2 s ! Indexing must be improved, and now we face the
difficult question once more: the cheapest adequate improvement or the best
index, or something in between?

Best Index for the Transaction

The ideal index is easy to derive. Candidate A is

(LNAME, CITY, FNAME, CNO)

Because this index has three stars, there is no need to derive candidate B. The
QUBE with this index is

Index LNAME, CITY, FNAME, CNO TR = 1 TS = 20

Fetch 20 × 0.1 ms

LRT TR = 1 TS = 20

1 × 10 ms 20 × 0.01 ms
10ms+0.2ms+2ms=12ms

This relates to the worst case with candidate A: at least 20 result rows. With
the ideal index, it takes 21 index touches to build a 20-row result table when the
whole result does not fit on one screen. The difference between the worst input
response times of indexes (LNAME, FNAME) and (LNAME, CITY, FNAME,
CNO) is about four orders of magnitude .