Join Methods

Abstracted from Oracle Online Document
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/optimops.htm#56299

1. Nested Loop

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 when two independent row sources are joined together.

2. Hash Join

The optimizer uses a hash join to join two tables if they are joined using an equijoin and if either of the following conditions are true:
A large amount of data needs to be joined.
A large fraction of the table needs to be joined.

(Note: The smaller one will be used to build the in-memory hash)

3. Sort Merge

The optimizer can choose a sort merge join over a hash join for joining large amounts of data if any of the following conditions are true:
The join condition between two tables is not an equi-join.
OPTIMIZER_MODE is set to RULE.
HASH_JOIN_ENABLED is false.
Because of sorts already required by other operations, the optimizer finds it is cheaper to use a sort merge than a hash join.
The optimizer thinks that the cost of a hash join is higher, based on the settings of HASH_AREA_SIZE and SORT_AREA_SIZE.

Note:
Oracle does not recommend using the HASH_AREA_SIZE and SORT_AREA_SIZE parameters unless the instance is configured with the shared server option. Oracle recommends instead that you enable automatic sizing of SQL work areas by setting PGA_AGGREGATE_TARGET. HASH_AREA_SIZE and SORT_AREA_SIZE are retained only for backward compatibility.