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

500 T 500 T

INO... ITEMNO...

ITEM

INVOICE

20,000,000 rows 100,000 rows

500 T

ITEMT, IEUR,
ITEMNO, INO

FF = 0.001%

TR = 1K
(1 + 500 + 500)

INVOICE_ITEM+

START

50,000,000 rows

Figure 8.19 Predicting table access order in a nested-loop join.

of touches to the indexes; those shown at the bottom right of each diagram
summarize the TRs —the major component. All accesses are index only.
The indexes designed for table access (INVOICE, INVOICE ITEM, ITEM)
provide a better access path than the alternative. However, 70,000 random
touches (Fig. 8.17) take 70 , 000 × 10 ms = 700 s, according to the QUBE. If
this is not satisfactory, the predicate columns would have to be copied to table
INVOICE ITEM. Then, with an index that contained all the predicate columns,
as shown in Figure 8.19, the number of random touches would be reduced to
1000—another example of the importance of the BJQ.
Would MS/HJ provide better performance? There are no local predicates on
table INVOICE ITEM, so with a MS/HJ, the first step would require 50,000,000
sequential touches; this would take 50 , 000 , 000 × 0 . 01 ms = 500 s, according to
the QUBE.
It would appear to be better to first join table ITEM with table
INVOICE ITEM using a nested loop, and then join the intermediate result with
table INVOICE using MS/HJ (Fig. 8.20). The ideal index on table INVOICE is
now one that starts with the local predicate columns (IEUR ... ).

Step 1: Join ITEM with INVOICE ITEM (NL)

TS = 0.01 × 100,000 = 1000 Index (ITEMT,...) TR = 1

TS = 0.01 × 50,000,000 = 500,000

Index (ITEMNO,...) TR = 1000

LRT = 1001 × 10 ms + 501 , 000 × 0 . 01 ms = 15 s

The work file contains 500,000 rows.