ASA SQL User's Guide
  Query Optimization and Execution
    Query execution algorithms
      Join algorithms

Nested block join and sorted block

The nested block join (also called the block nested loops join) reads a block of rows from the left hand side, and sorts the rows by the join attributes (the columns used in the join conditions). The left hand child of a nested block join is called a sorted block node. For each block of rows with equal join attributes, the right hand side is scanned once. This algorithm improves on the nested loops join if there are several rows on the left hand side that join with each row of the right hand side.

A nested block join will be chosen by the optimizer if the left hand side has many rows with the same values for join attributes and the right hand side has an index that satisfies the search condition.

Every nested block join has a left child that is a sorted block node. The cost shown for this node is the cost of reading and sorting the rows from the left input.

The left hand input is read into memory in blocks. Changes to tables in the left hand input may not be visible in the results. Because of this, a nested block join cannot provide sensitive semantics.

Nested block joins locks rows on the left input before they are copied to memory.