Re: ordering tables changes cost/plan in 9.2.0.6
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 30 Oct 2006 15:30:08 -0000
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
----- Original Message -----
From: "cosmin ioan" <cosmini@xxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Monday, October 30, 2006 3:13 PM
Subject: re: ordering tables changes cost/plan in 9.2.0.6
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
