| Query Tuning [message #170343] |
Wed, 03 May 2006 07:20  |
kumarsz
Messages: 180
Registered: March 2005
Location: canada
|
Senior Member |
|
|
Hi,
Oracle - Oracle 9i Release 2
In case of query involving join and filtering conditions (one or more) what should occur first - a join or filtering condition?
e.g.
select e.ename,d.dname from emp e, dept d where e.deptno = d.deptno and d.deptno = 40
Also
if i reverse the column names in join how the performance will be affected?
e.g.
select e.ename,d.dname from emp e, dept d where d.deptno = e.deptno and d.deptno = 40
Also since both emp and dept tables having indexes on dept column and statistics being taken recently will "driving table" concept matter here?
Thanks in Advance,
Pratap
|
|
|
|
| Re: Query Tuning [message #170455 is a reply to message #170343] |
Wed, 03 May 2006 22:44  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
When it has a choice, Oracle will apply filter conditions before join conditions because they are cheaper and usually filter out rows that do not then need to be joined. But consider your query:
select e.ename,d.dname
from emp e, dept d
where e.deptno = d.deptno
and d.deptno = 40
If this SQL uses a HASH or SORT MERGE join, then it will use the WHERE clause to filter dept before it joins. However if it performs a NESTED LOOPs join with emp as the leading table, and an index on d.deptno to lookup dept, then the join predicate will be applied first followed by the filter predicate.
| Quote: |
if i reverse the column names in join how the performance will be affected?
|
Reversing column names in the join has no effect.
| Quote: |
Also since both emp and dept tables having indexes on dept column and statistics being taken recently will "driving table" concept matter here?
|
The Driving Table concept applies always, except in a 2-table sort-merge join.
Ross Leishman
|
|
|
|
| Re: Query Tuning [message #170543 is a reply to message #170455] |
Thu, 04 May 2006 05:16 |
kumarsz
Messages: 180
Registered: March 2005
Location: canada
|
Senior Member |
|
|
Hi Ross,
Thanks for your quick reply.
Oracle : Oracle9i Enterprise Edition Release 9.2.0.7.0
Situation : EMP , DEPT both tables has index on deptno where deptno is Primary key for DEPT and deptno in EMP referes that in DEPT, Also EMP has non-unique index on Deptno
I got following Explain Plan for all the 3 queries (mentioned below the Plan)
Plan
-----------------------------------
SELECT STATEMENT
NESTED LOOPS
TABLE ACCESS BY INDEX ROWID DEPT
INDEX UNIQUE SCAN PK_DEPT
TABLE ACCESS BY INDEX ROWID EMP
INDEX RANGE SCAN FK_DEPT
1)select e.ename,d.dname
from emp e, dept d
where d.deptno = 40
and e.deptno=d.deptno;
2)select e.ename,d.dname
from dept d,emp e
where d.deptno = 40
and e.deptno=d.deptno;
3)select e.ename,d.dname
from emp e, dept d
where e.deptno = d.deptno
and d.deptno = 40;
Here you can see that inspite of Nested Loop no leading table is Full Scanned.
will you please "Explain Plan"?
Thanks and Regards,
Pratap Zope
|
|
|
|
| Re: Query Tuning [message #170717 is a reply to message #170543] |
Thu, 04 May 2006 22:39 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
It's driving off DEPT, retrieving all rows via the index where DEPTNO = 40. For each for (nested loops), it is retriving rows from EMP with DEPTNO matching the row from DEPT (ie. 40).
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
