Patrick,
I am not so sure whether the scenario you mention here i.e.,
"two tables that have about the same number of rows, or both
sides of the join have the same number of rows (If histograms
are used)", is the only selection criteria for the use of hash
joins by the optimizer. We don't claim to have seen everything
if life (Oracle included), but surely have not seen what you
mention. More importantly, we have seen hash joins in many many
more scenarios than the one you mention here.
In our experience (Oracle 7.3.4 - 8.0.6), we have seen hash
joins selected by the optimizer in quite a few scenarios, based
on the values of some key init.ora settings. I have already
written about that in yesterday's posting. However, in theory,
hash joins are designed for and are beneficial in the following
scenario:
"Applications that contain SQL that join tables, one of which is
very small (e.g. 1000 rows) and the other is significantly large
(e.g. 1000000 rows), and contain predicates that process a
significant portion of the larger table."
Please do not read the above scenario as "hash joins will work
for SQL that contains only 2 tables in the from clause". What I
am referring here, in the above scenario is a "join pair", which
means that hash joins will even work for multi-table joins, so
long as the "join pair(s)" in the SQL have been identified to
possess the aforementioned characteristic.
The aforementioned scenario/characteristic is typical in a lot
of batch jobs, which is mostly where hash joins increase
performance and perform better than Nested Loops. Hash Joins
may also benefit some components of a transactional system, but
that probably is an exception rather than the rule.
Best Regards,
Gaja.
- "Elliott, Patrick" <Patrick.Elliott_at_bestbuy.com> wrote:
> Have the number of rows changed significantly in any of the
> tables in
> relation to the others? I believe hash joins are only
> selected
> automatically by the optimizer when you are joining between
> two tables that
> have about the same number of rows, or both sides of the join
> have the same
> number of rows (If histograms are used).
>
> > -----Original Message-----
> > From: Dan.Hubler_at_midata.com [SMTP:Dan.Hubler_at_midata.com]
> > Sent: Monday, June 26, 2000 10:37 AM
> > To: Multiple recipients of list ORACLE-L
> > Subject: RE: Hash join not being used
> >
> >
> >
> > Thanks for the reply.
> > The optimizer has always been set to CBO and the tables have
> been analyzed
> > (10%)
> > on a weekly basis for many months. Just last week, we did
> analyze
> > (compute) on the
> > tables in question, with no change in the results.
> >
> > I should have included that information in my original post.
> >
> >
> >
> >
> >
> >
> > Diane Whitehead
> <Diane.Whitehead_at_palmerharvey.co.uk>@fatcity.com on
> > 06/26/2000 09:57:08 AM
> >
> > Please respond to ORACLE-L_at_fatcity.com
> >
> > Sent by: root_at_fatcity.com
> >
> >
> > Sent From the mail file of: Dan Hubler
> >
> >
> > To: Multiple recipients of list ORACLE-L
> <ORACLE-L_at_fatcity.com>
> > cc:
> >
> >
> >
> > If your optimizer is set to cost based and the tables
> concerned have been
> > analyzed between the change in execution plan, that could
> account for the
> > alteration.
> >
> > > -----Original Message-----
> > > From: Dan.Hubler_at_midata.com [SMTP:Dan.Hubler_at_midata.com]
> > > Sent: Monday, June 26, 2000 3:16 PM
> > > To: Multiple recipients of list ORACLE-L
> > > Subject: Hash join not being used
> > >
> > >
> > > We have a query that is a good candidate for a hash-join
> operation.
> > > As a matter of fact, when we force it to use a hash-type
> join (with a
> > > hint), the
> > > elapsed time of the query goes from 90 minutes to about 2
> minutes.
> > >
> > > However, we are unable to force it to use a hash-join,
> without using the
> > > hint.
> > >
> > > What perplexes us is that a month ago, the query was using
> a hash-join
> > on
> > > its
> > > own, without our intervention.
> > >
> > > Any clues as to why the optimizer would change its mind
> and stop using a
> > > hash-join?
> > >
> > >
> > >
> > > --
> > > Author:
> > > INET: Dan.Hubler_at_midata.com
> > >
> > > Fat City Network Services -- (858) 538-5051 FAX: (858)
> 538-5051
> > > San Diego, California -- Public Internet access /
> Mailing Lists
> > >
>
> > > 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).
> > --
> > Author: Diane Whitehead
> > INET: Diane.Whitehead_at_palmerharvey.co.uk
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858)
> 538-5051
> > San Diego, California -- Public Internet access /
> Mailing Lists
> >
>
> > 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).
> >
> >
> >
> > --
> > Author:
> > INET: Dan.Hubler_at_midata.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858)
> 538-5051
> > San Diego, California -- Public Internet access /
> Mailing Lists
> >
>
> > 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).
> --
> Author: Elliott, Patrick
> INET: Patrick.Elliott_at_bestbuy.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858)
> 538-5051
> San Diego, California -- Public Internet access /
> Mailing Lists
>
> 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).
Gaja Krishna Vaidyanatha
Director, I-O Management Products
Quest Software Inc.
(972)-304-1170
gajav_at_yahoo.com
Received on Tue Jun 27 2000 - 10:19:55 CDT
