| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: What is Hash Join?
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).
Received on Fri Jan 16 2004 - 07:24:50 CST
 |
 |