-
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
Anil
anil_tj@yahoo.com
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|