Relation Operator - Nested Loop Join

About

Nested loop joins are join operator.

They are useful when small subsets of data are being joined and if the join condition is an efficient way of accessing the second table.

NESTED LOOPS is a row operation.

A nested loop join involves the following steps:

  1. The optimizer determines the driving table and designates it as the outer table.
  2. The other table is designated as the inner table.
  3. For every row in the outer table, Oracle accesses all the rows in the inner table. The outer loop is for every row in the outer table and the inner loop is for every row in the inner table. The outer loop appears before the inner loop in the execution plan, as follows:
NESTED LOOPS 
        outer_loop 
        inner_loop 

When the Optimizer Uses Nested Loop Joins

The optimizer uses nested loop joins when joining small number of rows, with a good driving condition between the two tables.

You drive from the outer loop to the inner loop, so the order of tables in the execution plan is important.

The outer loop is the driving row source. It produces a set of rows for driving the join condition. The row source can be a table accessed using an index scan or a full table scan. Also, the rows can be produced from any other operation. For example, the output from a nested loop join can be used as a row source for another nested loop join.

The inner loop is iterated for every row returned from the outer loop, ideally by an index scan. If the access path for the inner loop is not dependent on the outer loop, then you can end up with a Cartesian product, for every iteration of the outer loop, the inner loop produces the same set of rows. Therefore, you should use other join methods as Oracle Database - Hash joins when two independent row sources are joined together.

Documentation / Reference

Task Runner