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

Sergey,

Just so you know, an alternative that can greatly speed your query performance is OMNIDEX advanced indexing, which has pre-joined indexes that accelerate cross table joins, and aggregation indexes that eliminate the need for a lot of aggregate tables.

See http://www.disc.com/dwhbroch.html or contact me for more information.

Cheryl Grandy
cgrandy_at_disc.com
303 444-4000
www.disc.com/home

 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.
>

--
Cheryl Grandy
DISC
Get OMNIDEX for the fastest
applications ever


Sent via Deja.com http://www.deja.com/
Before you buy.