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

There is no difference between the two indexes with regard to
performance—they both result in an enormous reduction in cost. Unfortunately,
a three-star index would imply an additional index because adding a column
(CITY) between LNAME and FNAME in the current index could adversely
affect existing programs. We have already discussed this problem in Chapter 4.
This is why we need to consider the cheaper alternatives first.

Semifat Index (Maximum Index Screening)

Adding the predicate column CITY at the end of the existing index (LNAME,
FNAME) eliminates most of the table touches because index screening will take
place. Table rows will only be accessed for index entries known to contain the
required CITY.
This index (LNAME, FNAME, CITY) passes BQ. All the predicate columns
are in the index. However, it has only one star; the required index rows are not
close to each other (we only have one MC) and, of course, the index is not fat.
But would this cheap solution be adequate?

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

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

Fetch 1000 × 0.1 ms

LRT TR = 1001 TS = 10,000

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

The original LRT has been reduced from nearly 2 minutes to 10 seconds with this
semifat index, a huge improvement, but not enough (remember the best index
was only 120 ms). We still have too many expensive TRs; we will need a fat
index to obtain decent performance.

Fat Index (Index Only)

Adding one more column to the semifat index makes the index fat: (LNAME,
FNAME, CITY, CNO). Now we have two stars, the second and third with an
LRT of 1 s.

TS = 1% × 1,000,000

Index LNAME, FNAME, CITY, CNO TR = 1

Table CUST TR = 0 TS = 0

Fetch 1000 × 0.1 ms

LRT TR = 1 TS = 10,000
1 × 10 ms 10,000 × 0.01 ms
10 ms + 0.1 s + 0.1 s = 0.2 s
Table 5.1 provides a comparison of the performance of the various indexes;
the last column shows the additional maintenance costs that would be incurred.
Note that the three-star index would be a new index. A reminder of these
costs follows: