| Execution plan [message #633171] |
Thu, 12 February 2015 00:42  |
 |
Prathikesh
Messages: 20
Registered: February 2015
Location: Hyderabad
|
Junior Member |
|
|
Hi
I have captured the execution plan of a query using the package "DBMS_XPLAN.DISPLAY". If we reorder the conditions in the WHERE clause, is the predicate information changes ? I think, considering the COST BASED approach, reordering the columns in the WHERE clause will not impact the performance plan. Please suggest. Below are few of the parameters that I have set for my database.
optimizer_mode string ALL_ROWS
optimizer_index_cost_adj integer 100
optimizer_features_enable string 11.2.0.2
timed_statistics boolean TRUE
|
|
|
|
|
|
| Re: Execution plan [message #633196 is a reply to message #633171] |
Thu, 12 February 2015 02:47  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Prathikesh wrote on Thu, 12 February 2015 12:12I think, considering the COST BASED approach, reordering the columns in the WHERE clause will not impact the performance plan.
Welcome to the forum!
A quote by T.Kyte on a similar question here -
Quote:the cost based optimizer is rather insensitive to the ordering of where clauses, it assigns costs in order to determine what to do first and how to do things.
the old unsupported rule based optimizer was sensitive to the ordering, but not so the CBO.
Will I guarantee the SAME EXACT PLAN based on predicates in different order with the CBO? No - ties (operations that are computed to take the same/similar amount of work) could result in different plans given different predicates.
But in short, as a SQL coder - you should put the predicate in an order that makes sense to you, makes the query more readable.
|
|
|
|
|
|
| Re: Execution plan [message #633539 is a reply to message #633506] |
Fri, 20 February 2015 16:31 |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
11gR2 Oracle automatically reorders predicates using a cost based approach anyway. So unless you have turned this off, your ordering is more or less ignored these days.
I even have an example of this in Chapter #2: Ways to use a Query Execution Plan, in my SQL Tuning book. It was a very interesting example for me as it involved a couple of unexpected co-operating problems that was fun to figure out. But the point is, what used to be true has "as usual" changed between releases.
Was there some specific reason you were asking this question or was it just for general knowledge?
Kevin
[Updated on: Fri, 20 February 2015 16:39] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
