First page Back Continue Last page Graphics

Nested Loops Join


Notes:

The nested loops is a common join technique – though becoming less common with the advent of the hash join (hash join is covered later in this lesson). With a nested loops, the optimizer chooses a driving (outer) table and an driven (inner) table. To reduce the number of scans of the inner table, the driving (outer) table is typically a small table (or a filter on the driving table reduces it to a small number of rows).
To reduce the cost of repeated scans of the driven (inner) table, the inner table will typically have a unique or selective index that can be used on the join column(s).
Nested loops starts returning rows very quickly – this is useful in situations where you intend to retrieve only the first “n” number of rows from the result or a process can start consuming rows before the entire set is constructed. However, if memory is not in short supply, or if the tables are large, or selective indexes are not available, this can be a poor join choice. In fact, CBO will probably choose a hash join instead.