Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: ordering tables changes cost/plan in 9.2.0.6
I guess it is notionally possible.
The optimizer does not work through all permutations of the table ordering; and - where there is no other determinant for order - the initial join order is dictated by the order of the tables in the FROM clause.
Consequently a change in the table order could mean that a plan was examined for one version of the query, but never reached for the other. You would probably need to do a careful check of all the join orders reported in the 10053 trace to see if this was the cause.
In passing, though, Wolfgang has discovered cases where the optimizer's evaluation of
t1.cola = t2.colb
is different from
t2.colb = t1.cola
i.e. simply reversing the order of operands in a single predicate results in a change in cost - from which I have constructed an example where exactly this swap results in a change in execution plan. So you may be seeing a bug.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
hello all,
I was under the impression that one would not have to worry about the table
order in an OPTIMIZER_MODE=CHOOSE type query. I was recently presented with a
query whereby the cost did not change that much, but the plan did change
drastically by just changing the tables' order in the "FROM" clause.
What can I infer from this? Is this "phenomenon" pretty widespread?
thx,
Cos
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.408 / Virus Database: 268.13.17/505 - Release Date: 27/10/2006
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Oct 30 2006 - 09:30:08 CST