Re: Performance and table order

From: Steve Phelan <stevep_at_no-spam.pmcgettigan.demon.co.uk>
Date: 1997/07/04
Message-ID: <33BCA972.16F65201@no-spam.pmcgettigan.demon.co.uk>#1/1

Robert Risholm wrote:

> Oracle 7.3 supports a COST based optimizer. The "order" of your
> statements
> will have no bearing on the execution path Oracle takes, it will make
> the
> decision based upon statistics.
>
> It appears you are using the RULES based optimizer used prior to 7.3.
> (Or
> possibly you have not run statistics for the COST based optimizer??)
>
> The COST based optimizer in my experience is very good at choosing the
> most
> efficient execution path. I recommend you have the COST based
> optimizer
> selected as the default for environment. Run ANALYSE STATISTICS on a
> regular basis.

Yep, agree with all the above points. However I have found a few situations where 'hand tuning' the SQL has improved the performance a lot. Also remember that you can use the 'ORDERED' hint to force a particular order - and the tables are processed in the *reverse* order from that when using RULE based optimisation (left-right rather than right-left, if I remember correctly).

Steve Phelan. Received on Fri Jul 04 1997 - 00:00:00 CDT