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:

Subject: RE: Hash join not being used

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