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

1. The number of random touches will be high, even with the best possi-
ble indexes.
2. Index design will become difficult because of complex joins.
3. The optimizer will make wrong choices because of complex joins.

There is a simple way to prevent these problems—any table design with a
1:1 or 1:C relationship should be justified by the person proposing it. There are
cases where table splitting is a good idea, or even necessary, but they are not at
all common with current hardware. The splitter should have the burden of proof.

EXERCISES

8.1. Estimate the elapsed time for the join shown in Figure 8.25, using the given fil-
ter factors.
8.2. Design the best possible indexes for the join without adding redundant table columns
and estimate the elapsed time.
8.3. There are three foreign keys in table CUST pointing to small code tables. Estimate
the local response time for the four-table join below with nested loop and the best
table access order (Fig. 8.26).

F,C

P,C P

SELECT A2, B1, B2
FROM A, B
WHERE A1 = :A1
AND
B1 > :B1
AND
A.AK = B.AK

BK

AK AK

FF = 1%

A B FF = 0.001%

10,000,000
rows

50,000,000
rows

Figure 8.25 Slow table join.

SQL 8.19

SELECT CNAME, C1TEXT, C2TEXT, C3TEXT

FROM CUST, C1, C2, C3

WHERE CUST.CNO = :CNO

AND
CUST.C1PK = C1.C1PK
AND
CUST.C2PK = C2.C2PK
AND
CUST.C3PK = C3.C3PK