Re: Hash join -vs- merge sort join

Emrynsoft <emrynsoft_at_aol.com> wrote in article <19970414154301.LAA16087_at_ladder01.news.aol.com>...
> 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.

I believe that Oracle have claimed that the hash join almost always perform as well as the sort-merge and will perform much better than sort-merge when the hash table fits in memory. Thus the hash join algorithm might be preferred by the optimizer in most or all circumstances.

I've only performed comparisons on small-medium sized tables, but I've never seen sort-merge outperform hash join.

-- 
Guy Harrison

gharriso@werple.net.au || http://werple.net.au/~gharriso || 613 419377964