Re: Poor hash Join performance

if the relative performance of the various join algorithms was the same, there wouldn't be much point in having more than one algorithm. i've typically seen the hash join chosen on much smaller result sets (e.g. <100 rows)

i've run explain plans and traces of plans, comparing hash join to sort merge and nested loops, and the hash join used fewer resources in every case that it was the plan selected by the optimizer (absent of any hints, with Oracle 8.0.6).

i've never really considered using the hash join with very large result sets, it just doesn't seem like it would an ideal approach.

did the query take 60 hours to complete ? is the hash join execution plan selected by the Oracle optimizer when no hints are specified ? are the statistics up-to-date ? have histograms been collected on any of the columns ?

<cshelt_at_acxiom.com> wrote in message news:94sn7j$cf3$1_at_nnrp1.deja.com...
> Hi all-
>
> I am extracting from and Oracle DB that is not running parallel
> server. I've noticed that Merge/Sort Joins are running at fair speeds
> while Hash joins are running insanely slow (1.5 million table and 2.5
> million table was going to take around 60 hours for hash and 1 hour for
> Merge). Here are some of the parameters
>
> Hash_Area_Size =20 meg
> Sort_Area_Size = 10 meg
> Hash_Multiblock_IO_count = 16 db_file_multiblock_read_count = 32
> db_block_size = 16k
>
> Is there anything else that might cause such a large difference in
> performance?
>
>
> Sent via Deja.com
> http://www.deja.com/
Received on Fri Jan 26 2001 - 20:06:38 CST