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

Table 8.1 Summary of the Local Response Times—1

Type Program A Program B

Current indexes 35 min 10 min

Ideal indexes 17 min 3.5 min

Ideal indexes, ideal program ( + ), multi-screen result 2 s

Ideal indexes, ideal program ( + ), single-screen result 3.5 min

(FF pitfall)

BJQ ideal indexes, ideal program ( + ), single-screen 0.2 s

result (FF pitfall)

We must stress once again, that our discussion of join methods is, as yet, far
from complete.

PREDICTING THE TABLE ACCESS ORDER

We have seen that with the nested-loop join method the table access order may
have a dramatic impact on performance—and indexing. The ideal indexes cannot
be designed until an assumption has been made about the best table access order.
The following rule of thumb, in most cases , predicts the best table access
order:
The outer table is the one with the lowest number of local rows .
The number of local rows is the number of rows remaining after the local
predicates have been applied using the maximum filter factors, as shown in
Figure 8.12.
This rule of thumb ignores the following factors:

1. Order by . In our simple case study, the sort for ORDER BY IEUR DESC
could only be avoided by making table INVOICE the outer table. This

CUST INVOICE

1,000,000 rows

20,000,000 rows

FF of local
predicates

10% 0.1%

Number of
local rows NLR

100,000 20,000

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