Home » RDBMS Server » Performance Tuning » Query Tuning
Query Tuning [message #170343] Wed, 03 May 2006 07:20 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: Number of transactions
Next Topic: Query Slow - any ideas how to speed it up??? here's what I have...
Goto Forum:
  


Current Time: Mon May 27 05:12:27 CDT 2024