Search notes:

Oracle: Hash Join

When Oracle hash-joins two data sets, the join-columns of the (typically?) smaller data set is first hashed and stored (in memory or on disk).
Then, Oracle iterates over each record of the larger result set and also hashes the each of the join-columns and looks that hash up in the stored away hashed set.
Hashing the smaller set first has three benefits:
A hash join is especially performant if the the smaller input set is smaller than n (for example n < 25000).
Hash joins can only be performed for equijoins.

See also

Events:
The init parameter hash_join_enabled.
The PGA has a hash area that is required for hash joins.
dba_join_ind_columns
The hash hint
The plan operation development/databases/Oracle/SQL/statement/execution/plan/operations/hash-join

Index