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
on 8.0.... "and a.deptid = b.deptid" will be excuted first. the way is buttom up in the where part of the sql.
According to me ,ur query will be executed in the following order
In case of any help needed please ask me at email@example.com
Rohit Nirkhe,Oracle DBA,OCP 8i
could you tell me why this is so.coz i feel where clause should be executed bottoms up.is this specific to a version????
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 ...
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
----------------------------------- ----------- --------------------
TABLE ACCESS FULL DEPT
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 ...
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.
Click Here to Expand Forum to Full Width