The most common join strategy in
Another type of join in
Nested loop joins are preferable in most situations.
Hash joins are preferable in situations in which the inner table values are unique and there are many qualifying rows in the outer table. Hash joins require that the statement's WHERE clause be an optimizable equijoin:
The hash table for a hash join is held in memory and if it gets big enough, it will spill to the disk. The optimizer makes a very rough estimate of the amount of memory required to make the hash table. If it estimates that the amount of memory required would exceed the system-wide limit of memory use for a table, the optimizer chooses a nested loop join instead.
If memory use is not a problem for your environment, set this property
to a high number; allowing the optimizer the maximum flexibility in considering
a join strategy queries involving large queries leads to better performance.
It can also be set to smaller values for more limited environments.