Re: Poor hash Join performance

As a very general guideline, a hash join is better when one of the tables is going to return a much smaller result set than the other table, and a sort/merge is better when the two tables are going to return similar volumes of data.

The ideal for a hash join is that Oracle should be able to turn the first data set into an in-memory hash of 2^N buckets. Where this cannot happen it is sometimes possible for a relatively small change in the hash_area_size and hash read size (I've can't remember the exact name) to make a huge
difference in performance. There is a brief note about this on my web site in the Addenda to Appendix C of the book, but I will be writing up a detailed example eventually.

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases

Publishers:  Addison-Wesley
More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



cshelt_at_acxiom.com wrote in message <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/