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

P,C U

LNAME,
FNAME

CNO

PNO CITY

CUST

1,000,000 rows

Figure 5.9 Cheapest adequate index or best possible index: Example 2.

Figure 5.9 shows the current indexes for the customer table. There are now
two possible indexes that should be considered, (CITY) and (LNAME, FNAME)
and so CURSOR56 is shown twice (SQL 5.6A and B), once for each index; note
that neither cursor is able to avoid a sort.
Neither index is semifat and so both will fail BQ. The QUBE shows very
poor performance; the result is identical regardless of which index is used. Each
can only use 1 MC in a matching index scan, each requires a sort, and neither
are index only. No stars at all, with correspondingly dreadful performance.

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

or CITY
Table CUST TR = 100,000 TS = 0

Fetch 10,000 × 0.1 ms

LRT TR = 100,001 TS = 100,000

100,001 × 10 ms 100,000 × 0.01 ms
1000 s+1s+1s=17min

With these indexes, many optimizers would actually choose multiple index access
(as discussed in Chapter 10); two index slices would be read, (LNAME, FNAME)
and (CITY) before accessing the table. This would take much less than 17 min,
but it would still take far too long.

Best Index for the Transaction

The best index is easy to derive. Candidate A is:

(CITY, LNAME, FNAME, CNO)