About the optimizer's choice of join strategy 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. Join strategieshow optimizer chooses

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.