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

design process, and as such, we should be aware of the trend towards table scans
and MS/HJ.

Ideal Indexes for Joins

Ideal indexes do not differ greatly according to the type of join. The ideal
index on an outer table in a NLJ will be identical to that for either table in
an MS/HJ—preferably a fat index that provides a very thin index slice. The only
difference occurs with the inner table of an NLJ; here the join column will be
part of the index, but again preferably a fat index that provides a very thin index
slice, and one that doesn't give rise to an excessive number of TRs.
Remember also that with MS, a sort may not be required on the outer table
if the access path provides the rows in join sequence; the join column should
therefore be included in the index, after any equal predicates. The CPU and
elapsed time savings involved in not having to create, sort, and access a work
file could be considerable.
We have seen that with HJ, the smaller row set is first stored in a temporary
table, hashed by the join column(s). The other table (or index slice) is then
scanned and for each row that satisfies the local predicates, the temporary table
is checked for matching rows, using the hash value.

JOINING MORE THAN TWO TABLES

When there are more than two tables in a join, it is not uncommon that some
connections are not supported by common columns (such as primary key and
foreign key). In Figure 8.16, tables INVOICE and ITEM can only be joined by
a Cartesian join, which would mean creating a temporary table containing all
combinations of local rows in the two tables, 20 , 000 × 1000 = 20 , 000 , 000 with

FF = 0.1% FF = 1%

INVOICE ITEM

20,000,000 rows
NLR = 20,000

100,000 rows
NLR = 1000

INO = INO ITEMNO = ITEMNO

INVOICE_ITEM

50,000,000 rows
NLR = 50,000,000

Figure 8.16 Joining more
than two tables.