Re: EXPLAIN PLAN

tj4_at_ornl.gov ( T L James) wrote:

>I have two SQL queries. They are identical except that two of the tables
>were swapped in the FROM clause. The explain plans are shown below. One of
>them runs very quickly; the other basically won't run. The plans are the
>same, except for the last few lines. Can anyone explain why there would be
>such a difference in performance? The query only returns 600 rows. The LAB_MEAS
>table has over 2 million records.
 

>Thanks for any help you can provide.
 

>Beverly Zygmunt
>Oak Ridge National Lab

The 2nd query plan show an extra SORT node - this is where your problem lies. The server is using rule-based optimisation. In order to use cost-based optimisation, which would have chosen the correct access path for you, you need to Analyze the tables involved.

Beware that Analyze can use *lots* of temporary space (maybe depending on which version of Oracle, but it can use up to 2 times base table size), so it may be best to use a low-ish estimated statistics command. RTM for more info. Also ensure that *all* tables involved in queries have stats or the optimiser can give you bad results (how would it know, otherwise ?).

Regards,
John. Received on Sun Feb 02 1997 - 00:00:00 CST