SQL and Access Paths › Access Paths › Nested Loop Join

Nested Loop Join

The nested loop join is the access method that DB2 has selected when joining two tables together and you do not specify join columns. A nested loop join repeatedly accesses the outer table as rows are accessed in the inner table. The nested loop join is most efficient when a few rows qualify for the inner table. The nested loop join is a good access path for transactions that are processing little or no data.

Note: Nested loop join is indicated using a value of 1 in the METHOD column of the PLAN_TABLE.

In a nested loop join, DB2 scans the outer or first table accessed in the join operation using a table space scan or index access. For each qualifying row in that table, DB2 searches for matching rows in the inner or second table accessed. DB2 concatenates any rows that it finds in both tables.

An index that supports the join on the outer table is important. For the best performance, that index should be a clustering index in the same order as the inner table. In this way, a join operation can be a sequential and efficient process.

DB2 can also dynamically build a sparse index on the outer table when no index is available on that table. If DB2 determines one of the following facts, it may sort the inner table:

• The join columns are not in the same sequence as the outer table.
• The join columns are not in an index.
• The join columns are in a clustering index where the table data is disorganized.