Skip to Main Content


Question and Answer

Tom Kyte

Thanks for the question, Venkat.

Asked: August 06, 2009 - 1:16 am UTC

Last updated: August 06, 2009 - 9:54 am UTC

Version: 10g

Viewed 1000+ times

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

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:

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.

and Tom 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.


  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment


Venkat Waran, August 06, 2009 - 10:00 am UTC

Thanks tom.

More to Explore


Get all the information about database performance in the Database Performance guide.