Re: EXPLAIN PLAN

In article <5cb39i$p69_at_stc06.ctd.ornl.gov>, T L James <tj4_at_ornl.gov> writes
>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
>
>

Without going into details of the queries, here is a possible cause. Under Oracle's rule based optimizer, if all tables in the query had the same 'weight', then it would pick the last table in the from clause as the driving table.

Example.

This is off the top of my head and not guaranteed to be 100% correct.

Assuming you have

table1
1 million rows
non-unique index on t1_name

table2
100 rows
non-unique index on t2_name

select *
table1,table2
where t2_name=t1_name;

The best thing to do here is to do a table scan on table_2 and use the index to find matching rows in table1. The wrong thing to do is to do a table scan on table1 and index into table2.

The rule based optimiser can't tell the difference and resorts to the extra rule outlined above. This has the strange effect you report above and can also make you look very good when you improve a query's performance by orders of magnitude after about 5 minutes work.

The cost based optimiser should have done away with this.

-- 
Jim Smith