Hi Lisa,
You're correct.
I'm sure you know, but I'd like to add that
hash join usually works faster than over
methods
when there are no good selective conditions
in the
clause WHERE.
Regards,
Ed
<BLOCKQUOTE
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
Hi Kevin,
It's quick if you have the temp space
to support it. however with larger tables my experience has been that it
blows temp, isn't that much faster even if you do have the temp space, and in
most cases you are better off with index-driven nested loops join. It
works well with small to medium-sized tables.
Just my .02
List, if I'm wrong, please correct
me.
Lisa Koivu <FONT
face="Book Antiqua" size=2>Oracle Database Administrator <FONT
face=Tahoma size=2>954-935-4117
The information in the electronic mail message is
Cendant confidential and may be legally privileged, it is intended solely for
the addressee(s) access to this internet electronic mail message by anyone
else is unauthorized. If you are not the intended recipient, any disclosure,
copying, distribution or any action taken or omitted to be taken in reliance
on it is prohibited and may be unlawful.
The sender believes that this E-mail and any
attachments were free of any virus, worm, Trojan horse, and/or malicious code
when sent. This message and its attachments could have been infected during
transmission. By reading the message and opening any attachments, the
recipient accepts full responsibility for taking protective and remedial
action about viruses and other defects. Cendant Corporation or Affiliates are
not liable for any loss or damage arising in any way from this message or its
attachments.
-----Original Message----- <FONT
face=Arial size=1>From: Kevin
Kostyszyn [SMTP:kevin_at_dulcian.com] <FONT face=Arial
size=1>Sent: Thursday, May
31, 2001 9:34 AM <FONT face=Arial
size=1>To: <FONT face=Arial
size=1>Multiple recipients of list ORACLE-L <FONT face=Arial
size=1>Subject: <FONT
face=Arial size=1>RE: what's hash-join
What's the performance (gain/loss) on such a
join, when would you want to use
one? Kev
-----Original Message----- <FONT
face=Arial size=2>McDonald Sent:
Thursday, May 31, 2001 6:00 AM To:
Multiple recipients of list ORACLE-L
A method of joining two tables. You scan
each and use a hashing algorithm to
isolate/match keys.
hth <FONT face=Arial
size=2>connor
- Senthil Ganapathi <FONT face=Arial
size=2><senthil.ganapathi_at_mannai.com.qa> wrote: > <FONT
face=Arial size=2>> Hi Rukmini >
could tell me what's that hash-join <FONT face=Arial
size=2>> > GSK <FONT
face=Arial size=2>> >
> <FONT face=Arial
size=2>> <FONT face=Arial
size=2>>
"Rukmini > <FONT face=Arial
size=2>>
Devi"
To: > Multiple recipients of list
ORACLE-L <FONT face=Arial
size=2>>
<rukmini_at_indb >
<ORACLE-L_at_fatcity.com> <FONT face=Arial
size=2>>
rain.com>
cc: > <FONT face=Arial
size=2>>
Sent
by:
Subject: > Re: 4 join
methods? <FONT face=Arial
size=2>>
root_at_fatcity. > <FONT
face=Arial
size=2>>
com > <FONT face=Arial
size=2>> > <FONT
face=Arial size=2>> >
<FONT face=Arial
size=2>>
31-05-01 > <FONT face=Arial
size=2>>
09:55 AM > <FONT face=Arial
size=2>>
Please > <FONT face=Arial
size=2>>
respond to > <FONT
face=Arial
size=2>>
ORACLE-L > <FONT face=Arial
size=2>> > <FONT
face=Arial size=2>> >
> <FONT face=Arial
size=2>> > <FONT
face=Arial size=2>> > 1.
Equi-join 2. Self-join 3.
Outer-join 4. > Hash-join
> >
rukmini > ----- Original Message
- > To: "Multiple recipients of
list ORACLE-L" >
<ORACLE-L_at_fatcity.com> > Sent:
Thursday, May 31, 2001 11:20 AM <FONT face=Arial
size=2>> > <FONT
face=Arial size=2>> > i feel the fourth one is self join.
> > <FONT face=Arial
size=2>> > ----- Original Message ----- <FONT face=Arial
size=2>> > To: Multiple recipients of list ORACLE-L <FONT
face=Arial size=2>> <ORACLE-L_at_fatcity.com> <FONT
face=Arial size=2>> > Sent: Thursday, May 31, 2001 8:10 AM
> > <FONT face=Arial
size=2>> > > > >
Hi, > > > <FONT
face=Arial size=2>> > > I just read there are four join
methods. I know > three:
> > > <FONT face=Arial
size=2>> > > Nested loops >
> > Sort merge > > > Hash
join > > > <FONT
face=Arial size=2>> > > What's the fourth? <FONT
face=Arial size=2>> > > >
> > - Greg > > >
> > > -- <FONT face=Arial
size=2>> > > Please see the official ORACLE-L FAQ: <FONT
face=Arial size=2>> <A target=_blank
href="http://www.orafaq.com">http://www.orafaq.com <FONT
face=Arial size=2>> > > -- >
> > Author: Greg Moore > >
> INET: sqlgreg_at_pacbell.net <FONT face=Arial
size=2>> > > > > > Fat
City Network Services -- (858) 538-5051 <FONT
face=Arial size=2>> FAX: (858) 538-5051 <FONT face=Arial
size=2>> > > San Diego,
California -- Public
Internet > access / Mailing
Lists > > > <FONT
face=Arial size=2>> <FONT face=Arial
size=2>--------------------------------------------------------------------
> > > To REMOVE yourself from this
mailing list, send > an E-Mail
message > > > to:
ListGuru_at_fatcity.com (note EXACT spelling of <FONT face=Arial
size=2>> 'ListGuru') and in > >
> the message BODY, include a line containing: <FONT
face=Arial size=2>> UNSUB ORACLE-L <FONT face=Arial
size=2>> > > (or the name of mailing list you want to be
> removed from). You may
> > > also send the HELP command for
other information > (like
subscribing). > > <FONT
face=Arial size=2>> > -- > >
Please see the official ORACLE-L FAQ: <FONT face=Arial
size=2>> <A target=_blank
href="http://www.orafaq.com">http://www.orafaq.com <FONT
face=Arial size=2>> > -- > >
Author: Saurabh Sharma >
> INET: saurabhs_at_fcsltd.com <FONT face=Arial
size=2>> > > > Fat City
Network Services -- (858) 538-5051 <FONT
face=Arial size=2>> FAX: (858) 538-5051 <FONT face=Arial
size=2>> > San Diego,
California -- Public
Internet > access / Mailing
Lists > > <FONT
face=Arial size=2>> <FONT face=Arial
size=2>--------------------------------------------------------------------
> > 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 <FONT face=Arial
size=2>> (like subscribing). <FONT face=Arial
size=2>> > -- <FONT
face=Arial size=2>> Please see the official ORACLE-L FAQ:
> <A target=_blank
href="http://www.orafaq.com">http://www.orafaq.com <FONT
face=Arial size=2>> -- > Author:
Rukmini Devi > INET:
rukmini_at_indbrain.com >
> Fat City Network Services
- (858) 538-5051 FAX: > (858)
538-5051 > San Diego,
California -- Public
Internet > access / Mailing
Lists > <FONT face=Arial
size=2>--------------------------------------------------------------------
> 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). > <FONT
face=Arial size=2>> >
> >
- > Please see the official ORACLE-L
FAQ: > <A target=_blank
href="http://www.orafaq.com">http://www.orafaq.com <FONT
face=Arial size=2>> -- > Author:
Senthil Ganapathi > INET:
senthil.ganapathi_at_mannai.com.qa <FONT face=Arial
size=2>> > Fat City Network
Services -- (858) 538-5051 FAX: <FONT
face=Arial size=2>> (858) 538-5051 <FONT face=Arial
size=2>> San Diego, California
- Public Internet > access / Mailing
Lists > <FONT face=Arial
size=2>--------------------------------------------------------------------
> 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).
"Some days you're the pigeon, some days you're
the statue"
<FONT face=Arial
size=2>____________________________________________________________
Do You Yahoo!? <FONT face=Arial
size=2>Get your free @yahoo.co.uk address at <A target=_blank
href="http://mail.yahoo.co.uk">http://mail.yahoo.co.uk <FONT
face=Arial size=2>or your free @yahoo.ie address at <A target=_blank
href="http://mail.yahoo.ie">http://mail.yahoo.ie <FONT
face=Arial size=2>-- Please see the
official ORACLE-L FAQ: <A target=_blank
href="
http://www.orafaq.com">
http://www.orafaq.com <FONT
face=Arial size=2>-- Author:
=?iso-8859-1?q?Connor=20McDonald?=
INET: hamcdc_at_yahoo.co.uk
Fat City Network Services --
(858) 538-5051 FAX: (858) 538-5051 <FONT face=Arial
size=2>San Diego, California --
Public Internet access / Mailing Lists <FONT face=Arial
size=2>--------------------------------------------------------------------
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 <FONT face=Arial
size=2>also send the HELP command for other information (like
subscribing).
- Please see
the official ORACLE-L FAQ: <A target=_blank
href="http://www.orafaq.com">http://www.orafaq.com <FONT
face=Arial size=2>-- Author: Kevin
Kostyszyn INET:
kevin_at_dulcian.com
Fat City Network Services --
(858) 538-5051 FAX: (858) 538-5051 <FONT face=Arial
size=2>San Diego, California --
Public Internet access / Mailing Lists <FONT face=Arial
size=2>--------------------------------------------------------------------
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 <FONT face=Arial
size=2>also send the HELP command for other information (like
subscribing).
Received on Thu May 31 2001 - 14:37:45 CDT