Additional Estimation Considerations - Relational Database Index Design and the Optimizers - page 281

an important consideration. The following comparisons are some of the more
important ones we have considered; we will now assess the CPU issues.

Fat Index or Ideal Index

This example from Chapter 6 showed that an ideal index could not really be
justified in terms of the elapsed time; in the summary we stated that:
Although there would be a further small advantage to be gained in using the
three-star index, particularly for the empty result table case, this would not be
significant and it would incur the overheads associated with a new index.
The QUBEs for the Fat and the Best indexes were:

Fat index: empty result set

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

Fetch 0 × 0.1 ms

LRT TR = 1 TS = 10,000

1 × 10 ms 10,000 × 0.01 ms
10ms+100ms+0ms=110ms

Best index: 20 result rows (1 screen)

Index LNAME, CITY, FNAME, CNO TR = 1 TS = 20

Fetch 20 × 0.1 ms

LRT TR = 1 TS = 20
1 × 10 ms 20 × 0.01 ms
10ms+0.2ms+2ms=12ms
If we now determine the CQUBE for the fat index and the best index:
Fat index 100 µ s × 1 + 5 µ s × 10 , 000 + 100 µ s × 0 + 10 µ s × 0 = 50 ms
Best index 100 µ s × 1 + 5 µ s × 20 + 100 µ s × 20 + 10 µ s × 0 = 2ms
we can see clearly that from the CPU point of view, according to our CPU coeffi-
cients, the ideal index would be 25 times better. Figure 15.5 shows a comparison,
LRT and CPU, for all the indexes considered in that example.
A new index would increase the CPU time for INSERT CUST and DELETE
CUST by only a fraction of a millisecond (one random touch and the write-related
CPU time). Therefore, the ideal index, alternative 3 in Figure 15.5, may well be
justified by the CPU time saving if the SELECT is executed frequently.

Nested-Loop Join (and Denormalization) or MS/HJ

The finalists in the join case study in Chapter 8 were
1. A BJQ join with nested loop (with table denormalization)
2. Ideal indexes for MS/HJ (no denormalization)