The augmented nested loop (ANL) is by far the most common join method. An augmented nested loop join is performed by doing a scan over the left subtree and for each row in it, performing an index bracket scan on a portion of the right subtree. The right subtree is read as many times as there are rows in the left subtree.
To be a candidate for an ANL join, the subtree pair for a join node must meet the following criteria:
There must be an index or indexes defined on the join columns for the table in the right subtree.
No other scan on that index has already been set.
When an ANL join is possible on several indexes, the least-cost index is chosen.
When there is an index defined on the left subtree's table instead of on the right, the optimizer analyzes the cost of swapping the subtrees to make an ANL join possible.
When neither subtree's table has an index defined on the join column, the optimizer analyzes the cost of creating a dynamic index on one or both of the subtrees.
There must be an index or indexes defined on the join columns for the table in the right subtree.