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

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 Sun Jul 30 2000 - 00:00:00 CDT