Once the join order has been established, each join node is analyzed to select from among the following algorithms:

  • Augmented nested loop (ANL) join
  • Merge join
  • Nested loop join

The optimizer generates, when possible, candidates for each algorithm. For each join node, candidates are generated by:

  • Checking whether the algorithm's requirements are satisfied. For example, the ANL join needs an index on one of the join columns.
  • Assuming the algorithm is usable, when multiple predicates reference the two tables being joined, choosing a predicate (or set of predicates) with the lowest cost.

Once a set of candidates exists, the optimizer selects the least costly candidate.