The optimizer compares the cost of choosing a hash join (if a hash join
is possible) to the cost of choosing a nested loop join and chooses the cheaper
strategy. For information about when hash joins are possible, see .
In some cases, the size of the hash table that would have to
build is prohibitive and can cause the JVM to run out of memory. For this
reason, the optimizer has an upper limit on the size of a table on which it
will consider a hash join. It will not consider a hash join for a statement
if it estimates that the size of the hash table would exceed the system-wide
limit of memory use for a table, the optimizer chooses a nested loop join
instead. The optimizer's estimates of size of hash tables are approximate
only.