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
#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 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.
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment