Proactive Index Design - Relational Database Index Design and the Optimizers - page 78

Basic Question for the Transaction

The BQ asks the question:

Is there an existing or planned index that contains all the columns referenced
by the WHERE clause?

In other words do we, or will we, have a semifat index ? The answer is clearly
no , at least as far as the current indexes are concerned. This doesn't necessarily
mean we have a problem; performance might still be satisfactory. But a warning
signal has at least been raised. We should determine if we do have a problem; if
we have, how serious would it be if we decided to go ahead anyway? should we
add the missing predicate columns to the index? This would provide a positive
answer to BQ, but we should bear in mind this still does not guarantee good
performance.

Quick Upper-Bound Estimate for the Transaction

The result rows are obtained in the requested order (ORDER BY FNAME), when
the DBMS chooses index (LNAME, FNAME); no sort is needed. Therefore, the
DBMS will not do early materialization : OPEN CURSOR generates no touches
but each FETCH generates touches to both the index and the table, some of
which will return a result row to the FETCH and some (the majority) that won't.
A matching index scan would take place with one matching column. The filter
factor of this column is 1%. According to the QUBE, the local response time of
the transaction that performs the following SQL

1001 × FETCH

OPEN CURSOR CLOSE CURSOR

will be:

TS = 1% × 1,000,000

Index LNAME, FNAME TR = 1

Table CUST TR = 10,000 TS = 0

Fetch 1000 × 0.1 ms

LRT TR = 10,001 TS = 10,000

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

The huge problem here, of course, is that the DBMS must read 10,000 nonadja-
cent table rows. This takes a long time, nearly 2 min .
The QUBE is an upper-bound formula; the resulting 100 s actually means
that the LRT may be up to 100 s. The actual LRT may be less, particularly if
there is a lot of memory and read cache compared to the database size. Many
random touches will not then cause a read from the disk drive, and the average
time per random touch may be less than 10 ms. In any case, this is not afalse
alarm. The number of random touches to the table is much too high. This issue
is discussed at length in Chapter 15.