Ask The Oracle Expert: Questions & Answers

Change the order of conditions in the WHERE clause

Change the order of conditions in the WHERE clause

By  Rudy Limeback, SQL Consultant, r937.com

SearchOracle.com

Let's say I have a select query like this: 

select *
from t1, t2
where t1.a = t2.a
and t1.b = t2.b
and t1.c = t2.c
and t1.d = t2.d

Can the order of the conditions in my WHERE clause change the time of execution? If t1.c and t2.c are indexed, would that speed things up if I do like this:

select *
from t1, t2
where t1.c = t2.c
and t1.a = t2.a
and t1.b = t2.b
and t1.d = t2.d

The answer is no. All database optimizers are smart enough to figure out which columns have indexes and to adjust the execution plan accordingly, no matter which sequence the conditions are presented in.