Re: What is Hash Join?

Hi!

Note that hash joins are very prone to extreme performance degradation when statistics outdated (don't reflect real rowcounts/sizes in tables).

When optimizer decides to go with hash join because it thinks that there is 1000 rows in driving table, but there is 1000000 instead, all driving table hash buckets (called build partitions) won't fit into hash area and they will "spill" over to disk causing a multipass operation which hit temp tablespace heavily.

So, with big tables, plan hash joins carefully and keep your stats up-to-date (up-to-reality).

Tanel.

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Friday, January 16, 2004 3:24 PM

>
> Thanks a lot for the information Kevin :), that's quite a light to me.
> So... Is it best to try to get hash join on execution plan at all time?
>
> Regards,
>
> Wendry.
>
> -----Original Message-----
> Sent: Thursday, January 15, 2004 9:09 PM
> To: wendry_yang_at_indo.net.id
>
> In my experience, Hash Joins have proven OUTSTANDING for joining two
> very
> large tables -- much faster than any other strategy.
>
> I copied the following from AskTom.oracle.com. It answers the question
> "What is the difference between a Sort Merge and a Hash Join. From
> AskTom.Oracle.com:
>
> "Well, a sort merge of A and B is sort of like this:
>
> read A and sort by join key to temp_a
> read B and sort by join key to temp_b
>
> read a record from temp_a
> read a record from temp_b
> while NOT eof on temp_a and temp_b
> loop
> if ( temp_a.key = temp_b.key ) then output joined record
> elsif ( temp_a.key <= temp_b.key ) read a record from temp_a
> elsif ( temp_a.key >= temp_b.key ) read a record from temp_b )
> end loop
>
> (its more complex then that, the above logic assumed the join key was
> unique
> --
> we really need to join every match in temp_a to every match in temp_b
> but
> you
> get the picture)
>
> The hash join is conceptually like:
>
> create a hash table on one of A or B (say A) on the join key creating
>
> temp_a.
>
> while NOT eof on B
> read a record in b
> hash the join key and look up into temp_a by that hash key for
> matching
> records
> output the matches
> end loop
>
> So, a hash join can sometimes be much more efficient (one hash, not two
> sorts)
>
> Hash joins are used any time a sort merge might be used in most cases.
> If
> you
> don't see hash joins going on, perhaps you have hash_join_enabled turned
>
> off..."
>
>
>
>
> >From: "Wendry" <wendry_yang_at_indo.net.id>
> >To: "LazyDBA.com Discussion" <oracledba_at_lazydba.com>
> >Subject: What is Hash Join?
> >Date: Thu, 15 Jan 2004 20:39:29 +0700
> >
> >
> >
> >
> >Dear all,
> >
> >I understand join is for joining two tables, I know nested loops and
> >sort-merge joins and I know how they work and on what situation they're
> >used best at. But I really don't understand hash join, how are they
> >working to join tables and on what situation they performed best? And
> >based on what does Oracle use hash join for joining tables in the
> >execution plan? If they use hash value, how does Oracle determine the
> >hash value when performing hash join?
> > I really in the dark here, please give me some light., Thank you all
> in
> >advance.
> >
> >Regards,
> >
> >Wendry
> >
> >
> >
> >--------
> >Get today's cartoon: http://www.LazyDBA.com
> >Please don't reply to RTFM questions
> >Oracle documentation is here: http://tahiti.oracle.com
> >To unsubscribe: send a blank email to oracledba-unsubscribe_at_LAZYDBA.com
> >To subscribe: send a blank email to oracledba-subscribe_at_LAZYDBA.com
> >By using this list you agree to these
> >terms:http://www.lazydba.com/legal.html
> >
>
> _________________________________________________________________
> Learn how to choose, serve, and enjoy wine at Wine @ MSN.
> http://wine.msn.com/
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Wendry
> INET: wendry_yang_at_indo.net.id
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tanel Poder
  INET: tanel.poder.003_at_mail.ee

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).