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

STAR T

P,C

CNO IDATE

CUST INVOICE

1,000,000 rows

20,000,000 rows

Figure 8.2 Invoice outer table.

query will therefore be considerably longer than the previous one as the QUBE
will show:
Index IDATE TR = 1 TS = 19

Table INVOICE TR = 20

Index CNO TR = 20

Table CUST TR = 20

Fetch 20 × 0.1 ms

LRT TR=61 TS=19

61 × 10 ms 19 × 0.01 ms
610ms+0.2ms+2ms=612ms

As before, there is no question as to which table will be the outer table, the
starting point. There are no local predicates for the customer table and so this
table will be the inner table.
Again the same process would apply where two separate SELECT state-
ments were used instead of a single join statement. The first would be a cursor
for the invoice access; the second would be a SELECT to provide the customer
information using the customer number provided by the cursor. Likewise, the
QUBE would be the same (except for the number of FETCH calls).

IMPACT OF TABLE ACCESS ORDER ON INDEX DESIGN

In the two previous examples the local predicates, together with the indexes
available, leave no choice with regard to the starting point. Unfortunately, this
isn't always the case as many people have discovered to their cost. We will now
consider one of these less straightforward cases by means of a case study.