Re: Hash join VS sort-merge, nested loops...

I agree. Here's what Harrison says on page 161:

and [a hash join] should outperform sort merge joins for large tables when one table is much larger than the other
...
The hash join requires no sorting...

There're many other great tips scattered around in that book.

Yong Huang

Andrew <abarnett_at_paws.aus.net> wrote in message news:8m2cp4$ssv$1_at_perki.connect.com.au...
> For my money, the clearest exposition of join types and strategies is
 Oracle
> SQL High Performance Tuning by Guy Harrison, Prentice Hall PTR, ISBN
> 0-13-614231-1.
>
> Andrew
> <sergey_s_at_my-deja.com> wrote in message
 news:8m1ma5$90a$1_at_nnrp1.deja.com...
> > Hello, everyone.
> >
> > I am in the process of documenting the work that I and others are doing
> > at a client. The client has a data warehouse, and we are building
> > aggregate tables for them. A couple of tables are huge - 3 billion and 4
> > million rows. Using USE_HASH hint when joining these produced a 29 hour
> > query. I decided to use the hint after talking to a few DBAs, but noone
> > could tell me why hash would be faster. Everyone just suggested that I
> > try it because hash is intended for data warehouses and it seems to
> > perform better with large data sets.
> >
> > Now, I need to document why I chose USE_HASH and not some other join
> > type and also compare the available join types and, if possible, list
> > some "rules of thumb" for choosing one join or the other. I've read the
> > Oracle books and docs and they explain what the different joins do, but
> > none of them explain how to pick the appropriate one.
> >
> > Could you, please, help me or, perhaps, direct me to some literature
> > that gets into that?
> >
> > Thank you!
> >
> > Sergey
> >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
>
>
Received on Mon Jul 31 2000 - 00:00:00 CDT