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

I've got the book and it's great. Thank you all. Among lots of goodies in the book, I found one sentence that pretty much formulated my question and answered it. It said that it is impossible to predict which join will do better - hash or sort-merge (once it comes down to that), one would have to try it out. And trying out is exactly what I am having difficulty with due to client's resource constraints as well as financial ones. There is only one machine and it's for development and mission critical stuff. Try outs last for days because the tables involved are hundreds of Gigabytes (it's a data warehouse) and one of the tables has to be scanned entirely due to "tough" business requirements. However, I am positive that hash is the best choice in my case (in addition to parallel execution).

In article <8m1ma5$90a$1_at_nnrp1.deja.com>,   sergey_s_at_my-deja.com wrote:
> 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.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Aug 03 2000 - 00:00:00 CDT