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

C

ZIP LNAME FNAME

30103 JONA MARIA

T T

30103 JONES ADAM
30103 JONES ANNE

T T

T T T T T
T
T

T T T T T
T

30103 JONES ZAK

30103 JONI BETTY

1,000,000 index rows 1,000,000 table rows

Figure 5.4 Clustering index access.

until it finds an index row that does not have the right values for the columns ZIP
and LNAME. Including the last touch to a nonqualifying index row, the number
of sequential touches to the index is 1000. Thus, scanning the index slice takes,
according to the QUBE, 1 20 ms.

Because column CNO is not in the index, it must be read from the table.
As the index is clustering and assuming the 1000 table rows are adjacent, the
cost of scanning the table slice will be 1 × 10 ms + 999 × 0 . 01 ms = 20 ms (we
described how to count touches earlier in this chapter). In addition, there will
be the cost of the FETCH calls, making the total elapsed time, according to the
QUBE, of 140 ms. The cost of the table access is very low because sequential
touches are very cheap. The largest component is the processing associated with
the FETCH calls.
Index ZIP, LNAME, FNAME

× 10 ms + 1000 × 0 01 ms =

.

TR = 1 TS = 1000

Table CUST TR = 1 TS = 999

Fetch 1000 × 0.1 ms

LRT TR = 2 TS = 1999

2 × 10 ms 1999 × 0.01 ms
20 ms + 20 ms + 100 ms = 140 ms

The number of sequential touches to the table as shown above gives a mis-
leading impression of the accuracy of the QUBE figures. We have written the TS
figure as 999 simply to show how we can count the touches in an example such
as this. In future, we will not be quite so precise; it will be sufficient to show
the figure rounded up to 1000.