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

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