Help on SQL needed

I have a query like this :

select * from emp_salary a, department b
where location = 3
and b.deptid= 10
and a.deptid = b.deptid;

Which condition gets executed first? the one with location =3 or a.deptid=b.deptid or b.deptid=10 ???

location=3 first and then in that order

Hi

on 8.0.... "and a.deptid = b.deptid" will be excuted first. the way is buttom up in the where part of the sql.

Hi,
According to me ,ur query will be executed in the following order

1. a.deptid=b.deptid
2. location=3
3. b.deptid=10


In case of any help needed please ask me at rohitsn@altavista.com

Regards,
Rohit Nirkhe,Oracle DBA,OCP 8i
rohitsn@altavista.com

hi rohit

could you tell me why this is so.coz i feel where clause should be executed bottoms up.is this specific to a version????

regards
hrishy

Hi rohitsn

why? can you give me a referrence in ther documentation where i can read about your logic? ican only tell you that my answer is based on testing, but only simple tests.

to be more generic, I'd say that it depends on your optimizer mode, on the freshness of the stats, and on the indexes ...

hi pipo

you are rihgt, but let's try this scenario. no hint's and no cost optimizer. what will be the sequence then?

well, testing on MP and DEPT demo tables, without any index, I get the explain plan :

Operation Option Obj Name
----------------------------------- ----------- --------------------
SELECT STATEMENT
MERGE JOIN
SORT JOIN
TABLE ACCESS FULL DEPT
SORT JOIN
TABLE ACCESS FULL EMP

that is to say : FULL on EMP, FULL on DEPT where LOC=3 and DEPTNO=10, and then merge join the 2 temporary results on deptno ...

Questions:

1 - Why not just run it and get the plan?

2 - Why would you still be doing RBO anyway? ;)

Note: With multiple-table statements, prefix *every* column with either a table name or alias:
"where location = 3" is bad form.


- Chris