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

With the empty result set every index row found in the 1% predicate
LNAME : LNAME index slice has to be examined, but the result of the CITY

screening is clearly reflected in the table touch figures.

=

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

Table TR = 0 TS = 0

Fetch 0 × 0.1 ms

LRT empty result set TR = 1 TS = 10,000

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

Fat Index (Index Only)

Adding one more column to the semifat index evaluated above makes the index
fat: (LNAME, FNAME, CITY, CNO).

TS = 20 × 10

Index LNAME, FNAME, CITY, CNO TR = 1

Fetch 20 × 0.1 ms

LRT 1000 result rows TR = 1 TS = 200

1 × 10 ms 200 × 0.01 ms
10ms+2ms+2ms=14ms

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

Fetch 0 × 0.1 ms

LRT empty result set TR = 1 TS = 10,000

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

The local response time with the empty result set remains 0.1 s. Transactions
that produce one screen of a multiscreen result are now very fast because the 20
TRs to the table are no longer required.

Summary

1. The worst case depends on the index used:
ž For the original

index it is the empty result table (most common

LNAME and uncommon CITY).
ž For the semifat index, although in our example the large result table is
the worst case, it could be either result as determined by the relative
cost of the table access and the index slice scan. 20 TRs (200 ms)
is (according to the QUBE) equivalent in elapsed time to 20,000 TSs
(200 ms). With fewer rows per screen or a thicker index slice, the empty
result table could become the worst case.
ž For the fat index it is the empty result table.
ž For the three-star index it is the large result table (a single touch is
required for the empty result table).