|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Order of WHERE columns doesn't matter? The optimizer will handle that? [message #618461 is a reply to message #618205] |
Sat, 12 July 2014 01:35   |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
I remember back in 1989 (+- a year or two) some guy doing a presentation at an Oracle conference on this specific topic. Yea, in the early days a whole one hour session could be had on something as simple as this. Ah well... So far what has been said is true but there are a few exceptions to note. Here is a summary.
In the past (pre 11g days), the order of predicates in the WHERE clause could make a difference in performance. This is because not all predicates are equal in their ability to remove rows from a result set and since any row removed does not have to be checked for additional predicates once it has been removed, the faster you remove rows the fewer predicate tests that get applied to the result set overall and thus you get a savings in CPU on the query.
BUT... to my understanding, this only applies to FILTER operations, not ACCESS operations, both of which are visible via the predicate section of the query plan. This has a significant implication. It means that this tuning is almost exclusively only of value in inefficient queries that could benefit from other tuning, or in queries doing FULL TABLE SCANS. If your query is doing partition pruninig or index lookups or joins, then the predicates that participate in these operations are not affected by ordering. This reduces the number of predicates left to be ordered in real world queries by a considerable margin.
In 11g, the optimizer uses statistics to compute the selectivity of predicates and then automatically does the predicates in the order it thinks is most optimal. But there are two problems with this you should keep in mind.
1. this is subject to all the issues of statistics. If the optimizer has to guess on the statistics of a predicate then it may not be ordered in the best order.
2. there is a potential "bug" related to user defined functions. Technically it is not a problem on the oracle side since semantically it should not matter what order predicates are evaluated. But since changing the order of predicates means that intermediary surviving rows change during predicate evaluation, it is likely to change the set of rows that are fed into a user defined function. This should not matter unless the function has a bug in it. It would be a logical bug in the function, not in oracle's reorder of the predicates. I had this problem on a migration to 11g and had to get the developers to fix their code because they assumed that since there was a predicate of AND COLX IS NOT NULL in the WHERE CLAUSE, that no rows would go to the function with a null value in this column. Thus the function did not check for nulls in this column. When 11g reordered the predicates, all of a sudden there were rows going to the function with nulls in the associate column parameter. The function had a bug that caused an infinite PL/SQL loop when a null value was fed into this parameter, so the query went from fast to never finishing (took a while to hunt this one down).
But in the end everyone gets it wrong. Here is the real rule.
There is so much stuff you can be doing to make your system go faster, that if you are spending your time looking at order of predicates, you are wasting it. This is nothing more than an interesting topic in the annals of useless tuning strategies and is why it is a good thing Oracle now does it for us automatically in an attempt to squeeze out a few more milliseconds.
Have fun talking about it. It is a good brain exerciser. But don't waste your time doing it. You should be looking at stuff like design issues, statistics collection, bad coding practices, and you guys can help me out here with 101 other better ways to spend your time tuning an Oracle system.
Kevin
|
|
|
|
| Re: Order of WHERE columns doesn't matter? The optimizer will handle that? [message #618486 is a reply to message #618461] |
Sat, 12 July 2014 07:18 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Kevin,
I wouldn't dispute order of predicates is even close to be on a list of things one would look to tune. But your example with UDF is one of the cases when it might be. OK, developers fixed the bug in UDF. But it didn't change execution plan and UDF was called before AND COLX IS NOT NULL thus, most likely, resulting in sub-optimal performance (calling UDF too may times) unless you created extended stats for that UDF. But in some cases it isn't possible. For example, products like Siebel have their own stats collection which one doesn't want to mess with too much. So if we need to write non-Siebel code (e.g. wite a report or run ad hoc query) involving Siebel tables (and with Siebel tables 100 million rows is more a rule than an exception) SPM and hints become important tool. Another example is volatile tables where we modify data daily (or even more often) in large volumes. In such situations one needs to weigh stats collection time with performance improvement up-to-date stats provide. Do we want to collect stats if collecting up-to-date stats takes 55 min but most queries run 20 minutes with stale stats and 10 min with up-to-date stats? May be yes or may be not. We need to evalue the rest of the queries. If there are few that run 2 hours with stale stats and only 5 min with up-to-date stats we might look at such queries and apply either SPM or hints (and one of them could be ordered predicates).
Anyway, my point is - I like Oracle providing hints. No, I like Oracle providing multiple tools/ways of query optimization and hints being one of the tools. Using right tool for specific case is a different story.
SY.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
