Factors Affecting the Index Design Process - Relational Database Index Design and the Optimizers - page 101

Table 6.1 Comparison of the Worst Input QUBE

Type Index LRT Maintenance

Existing LNAME, FNAME 100 s —

Semifat LNAME, FNAME, CITY 0.2 s U CITY + 10-20 ms

Fat LNAME, FNAME, CITY, CNO 0.1 s U CITY + 10-20 ms

3 ∗ LNAME, CITY, FNAME, CNO 0.01 s I/D + 10 ms U + 10-20 ms

LRT is for the worst input QUBE; I = insert; D = delete; U = update

2. The original index would be a disaster, not with a large result table but
with an empty one. This is due to the huge number of table accesses
carried out to check the CITY.
3. Both the semifat index and the fat index provide adequate performance,
even with the empty result table. Both indexes avoid the unnecessary
table accesses, and the cost of scanning the index slice is relatively small.
The local response time also grows relatively slowly as the slice defined
by column LNAME grows. If the company obtains a lot of customers
from South Korea, the LEE slice may one day consist of 100,000 index
rows. Both indexes would still provide adequate response times with this
large result set; the worst input response would now be 1 s (100 , 000 ×

0 . 01 ms).
4. The advantage of using the fat index in preference to the semifat index
would be to avoid accessing the table for the first 20 result rows.
5. 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 (but note point 6 below).
6. If the LEE slice becomes too thick, a three-star index may be justified;
there will be a limit, perhaps of the order of 100,000 TSs, when scanning
an index slice just takes too long and a new index, a three star in this
case, should be created.
A summary of the various indexes we have considered during this analysis is
shown in Table 6.1.
Before we leave this example, we should explain that we have been tacitly
assuming that the empty result set may indeed be the worst case. We should
appreciate, however, that an almost empty result would have been even worse,
causing up to 20 table accesses in the case of a semifat index.

EXERCISES

6.1. The SELECT in Figure 6.5 takes up to 1 min with the current indexes. Design the
best possible indexes: (1) without adding a third index and (2) with a third index.