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

This index only has two stars because a sort is required (the ORDER BY column
follows a range predicate column). Therefore candidate B must be:

(CITY, FNAME, LNAME, CNO)

This also has two stars, but it has the second star instead of the first. The
QUBE will easily determine which one is the best.

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

Fetch 10,000 × 0.1 ms

LRT for candidate A TR = 1 TS = 10,000

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

TS = 10% × 1,000,000

Index CITY, FNAME, LNAME, CNO TR = 1

Fetch 10,000 × 0.1 ms

LRT for candidate B TR = 1 TS = 100,000

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

The best index is clearly candidate A (CITY, LNAME, FNAME, CNO) with
an LRT of 1 s; even the best index is now a borderline case.
We suggested earlier that for the purpose of comparing alternative access
paths, the FETCH processing may be ignored because it will be the same in
all cases. In doing this, however, one has to be a little careful when drawing
conclusions about the need for changes to the indexes. For example, in this case,
ignoring the FETCH processing would suggest that candidate A (LRT 0.1 s)
would be 10 times cheaper than candidate B (LRT 1 s), whereas the true advan-
tage is proportionally far smaller (2 to 1).
The cost of the sort required by candidate A is trivial in this example com-
pared to the 1-s saving over candidate B; in fact the sort cost has really been
absorbed in the FETCH cost as described earlier. The problem with candidate B
is the large number for TS because we are using a thick index slice (10%).

Semifat Index (Maximum Index Screening)

Adding the missing predicate column at the end of the two existing indexes
would eliminate many of the huge number of TRs to the table because index
screening would take place. Table rows will only be accessed for index entries
that are known to contain both the required CITY and the LNAME columns.
The two semifat indexes we could use with the existing indexes would be:

(CITY, LNAME) or (LNAME, FNAME, CITY)

The QUBE will again determine which one is the best.