Re: When does a merge join beat a hash join?

"Jaap W. van Dijk" <j.w.vandijk.removethis_at_hetnet.nl> wrote in message news:tjeb6vk2brd93jtbuopirauuset9g2vimq_at_4ax.com...
> A general (theoretical? academical?) question:
>
> When the Oracle optimizer chooses a merge join, why doesn't it choose
> a hash join instead?
> Shouldn't a hash join always be faster than a merge join?
> And if it isn't, is that because of poorly chosen parameter values, or
> are there data situations in which it is inherent that a merge join is
> faster than a hash join?

Hi Jaap,

Consider the steps required for a hash join. Oracle needs to read a table and "appropriately" cache this table so that it can be efficiently lookup up by the outer table. Now this doesn't necessarily mean the table has to be cached in it's entirety however if it's a real big mamma of a table and the poor hash_area_size just doesn't cut it and a massive amount of I/O to the temporary tablespace results, perhaps it would be better to suffer the overhead of reading/sorting the table once. So in situations where you have two very large tables and not enough memory, then a merge join will win out.

BTW, one of the nice things with the PGA_AGGREGATE_TARGET (when one eventually gets it to work) is that the "tuning" of the hash_area_size is implicit and potentially a lot more memory is available "safely" for such operations to occur efficiently.

Cheers

Richard Received on Wed Mar 05 2003 - 07:39:28 CST