Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: ordering tables changes cost/plan in 9.2.0.6

Re: ordering tables changes cost/plan in 9.2.0.6

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 30 Oct 2006 15:30:08 -0000
Message-ID: <020501c6fc38$48885460$0200a8c0@Primary>

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US