Indexing for Table Joins - Relational Database Index Design and the Optimizers - page 147

Apart from the choice of outer table, the only difference between the QUBEs
for programs A and B compared to program C will be the number of FETCH
calls:

200 , 000 × 0 . 1ms = 20 s Program A

, × . =

Program B 40 000 0 1ms 4s

2s
This benefit is actually very small in terms of the overall figures although it
could, of course, be significant in other circumstances.

Program C 2000 × 0 . 1ms = 0 .

Conclusion:CurrentIndexes

It is clear that the current indexes are not adequate with the worst input. We
should now consider the best affordable indexes, probably starting by designing
and evaluating the ideal indexes.

Ideal Indexes

ProgramA:OuterTableCUST
Using the approach discussed in Chapter 4 for the design of ideal indexes, can-
didate A for CURSORC is (CCTRY, CNO, CNAME, CTYPE). It is a three-star
index. Candidate A for CURSORI (CNO, IEUR DESC, INO) also has three stars.
The CNO column, of course, comes from CURSORC; we have now a thin index
slice based on CNO and IEUR. As both are ideal indexes, there is no need to
consider candidate B. These indexes are shown in Figure 8.7. Strictly speaking,
CURSORI does not require IEUR to be a descending key in the ideal index

MC = 2

100 KT

102 KT

F,C,U

U

STAR T

CCTRY,
CNO,
CNAME,
CTYPE

CNO,
IEUR DESC ,
INO

P,C P

CNO INO

CUST INVOICE

1,000,000 rows 20,000,000 rows

Figure 8.7 Program A with ideal indexes.