While trying to troubleshoot an application connectivity issue today along with a developer I faced an interesting situation. We had to run a SELECT on a fairly large table to check for a particular value. The search query I wrote had 2 WHERE clauses in it. One a DATE column and another was a VARCHAR2. I ran my query as below.
SELECT * FROM TABLE WHERE NAME=’JOHN’ and DD_DATE < SYSDATE – 30;
To my surprise the developer just jumped in and said that your query isn’t efficient you have to use the DD_DATE column in the where clase first and the varchar field last. For a moment he caught me off-guard. He look the confidence out of me. I looked at him and confidently said it doesnt matter. But he was adamant and said it did matter. But i stuck to my point. After a lot of arguing and counter-arguing. Touche !! Yours truly came out on the top. Though I managed to convince him, I didnt have the answer to Why the Where clause order in a select statement doesnt matter. The gut feeling inside me and years of experience working on Oracle told me that the SQL statement is parsed as a whole by the engine. But i didnt have an exact answer; till ofcourse I came home and googled a bit. And I found the answer on ASK TOM blog as usual. Tom Kyte the expert in Oracle database explains why. I will quote him.
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.
So what that means is that the order in the where clause has nothing to do with how fast the query will execute. The cost-based optimizer ignores the order of the predicates and parses the query as a whole.
Well that proves two things, experience counts for something and developers as usual dont get along to well with DBA’s 😀