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
Received on Mon Jun 26 2000 - 13:57:38 CDT
