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

the assumed filter factors (the local predicates are not shown in the diagram).
As this is a relatively expensive process, the table access order suggested by
the NLRs (the number of local rows)—ITEM, INVOICE, INVOICE ITEM—is
probably not the fastest one.
The only alternatives that are possible without a Cartesian join are shown in
Figures 8.17 and 8.18. The touches shown above each index are the total number

START

20 KT 50 KT

FF = 0.1% FF = 1%

IEUR...

ITEMNO...

50 KT

INVOICE

ITEM

20,000,000 rows

INO...

100,000 rows

TR = 70K
(1 + 20K + 50K)

INVOICE_ITEM

50,000,000 rows

Figure 8.17 Predicting table access order—1.

START

500 KT 1 KT

FF = 0.1% FF = 1%

INO... ITEMT...

INVOICE ITEM

500 KT

20,000,000 rows

100,000 rows

ITEMNO...

TR = 501K
(1 + 1K + 500K)

INVOICE_ITEM

50,000,000 rows

Figure 8.18 Predicting table access order— 2.