Venkat -- Thanks for the question regarding "SQL Query where clause order", version 10g

You Asked

Hi Tom,

I have a SQL query like below:

desc dept
deptno varchar2(10)
dname varchar2(10)

10 records in dept table

desc emp
empno varchar2(20)
deptno varchar2(10)
proj_no varchar2(20)
10,000 records in emp table

desc sal
empno varchar2(20)
salary number
10,000 records in sal table


desc proj
proj_no varchar2(20) (PK)
proj_name varchar2(100)
100,000 records in proj table


#1
select d.*
from dept d, emp e, sal s, proj p
where d.deptno=e.deptno
and e.empno=s.empno
and e.proj_no=p.proj_no


We would like to know if we change the order of join clauses in the where clause.

For example:

#2
select d.*
from dept d, emp e, sal s, proj p
where e.deptno=d.deptno
and e.proj_no=p.proj_no
and s.empno=e.empno

Is Oracle internally handles #1 and #2 in the same way or different way?

Thanks in advance.
Venkat

and we said...

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.

Reviews
August 6, 2009 - 10am Central time zone Bookmark | Bottom | Top Reviewer: Venkat Waran  Thanks tom.

---

Write a Review