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

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

Table CUST TR = 10,000 TS = 0

Fetch 10,000 × 0.1 ms

LRT TR = 10,001 TS = 10,000

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

TS = 10% × 1,000,000

Index LNAME, FNAME, CITY TR = 1

TR = 10% × 100,000 TS = 0 Table CUST

Fetch 10,000 × 0.1 ms

LRT TR = 10,001 TS = 100,000

10,001 × 10 ms 100,000 × 0.01 ms
100s+1s+1s=102s

Although we have a thicker slice with the second index, this factor is heavily
outweighed by the huge (albeit much reduced because of the screening effect)
number of table accesses. It looks as though we will need a fat index though to
eliminate the 10,000 TRs to the table.

Fat Index (Index Only)

Adding two more columns to the first semifat index evaluated above and one more
to the second will make both indexes fat: (CITY, LNAME, FNAME, CNO) or
(LNAME, FNAME, CITY, CNO).

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

Fetch 10,000 × 0.1 ms

LRT TR = 1 TS = 10,000

1 × 10 ms 10,000 × 0.01 ms
10ms+0.1s+1s=1s

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

Fetch 10,000 × 0.1 ms

LRT TR = 1 TS = 100,000

1 × 10 ms 100,000 × 0.01 ms
10ms+1s+1s=2s

Now that the table accesses have been eliminated, the difference between the
LRTs of the two indexes is apparent. The first index has the first star, providing
a thin index slice; the second does not and so uses a thicker index slice.
The first index is, of course, the best index we derived earlier for candidate
A. This is because the original index on which it is based only contained CITY,
the only equal predicate in the SELECT. So we were able to build it up into the
best index, first by adding the BETWEEN predicate column (making it semifat,
which was still not adequate), and then by adding the other columns from the
SELECT (making it fat).
Table 5.2 provides a comparison of the performance of the various indexes.