Hash join -vs- merge sort join

Hello,

I am trying to figure out when it is better to use a hash join over a merge sort join. My understanding was that hash join works better if the table on which the bitmap is created is small enough to be held in memory. But I have noticed that the optimizer sometimes even chooses hash join for joins of 2 very large tables, neither of which would completely fit in memory. In fact it seems to me that Oracle optimizer always seems to choose a hash join. It also seems that hash join is a better method for most cases over merge-sort (just a very unscientific real-time observation).

Any insights into the working of the optimizer for the hash join would be greatly appreciated.

Thanks,
Abhijit Received on Mon Apr 14 1997 - 00:00:00 CDT