Home » RDBMS Server » Performance Tuning » Performance in joins (Oracle,9i,AIX 5.2)
Performance in joins [message #404223] Thu, 21 May 2009 00:28 Go to next message
shahnazurs
Messages: 240
Registered: June 2005
Location: India
Senior Member
Can you please let me know the difference in the following two queries in performnace?

1)

select e.* from emp e,dept d
where e.deptno=d.deptno

2)

select e.* from emp e
inner join dept d
on(e.deptno=d.deptno)




Re: Performance in joins [message #404252 is a reply to message #404223] Thu, 21 May 2009 02:20 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
None at all. Oracle uses the same underlying methods to join data

Ross Leishman
Re: Performance in joins [message #404260 is a reply to message #404223] Thu, 21 May 2009 02:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1) is Oracle syntax
2) is ANSI/ISO syntax

Regards
Michel
Re: Performance in joins [message #404262 is a reply to message #404260] Thu, 21 May 2009 02:36 Go to previous messageGo to next message
shahnazurs
Messages: 240
Registered: June 2005
Location: India
Senior Member
I believe 1st one is Oracle Syntax and next one is ANSI one? and also the following statement as well

select * from emp e
where exist (select '1' from dept where deptno=e.deptno)


Kindly let me know amongst these three which one is faster and explanation.
Re: Performance in joins [message #404266 is a reply to message #404262] Thu, 21 May 2009 02:48 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
None at all (I can keep doing this all day if you like)

Recent releases of Oracle are able to unnest subqueries and execute them as joins. Assuming DEPT.DEPTNO is unique, the semi-join that is executed will be functionally identical to a regular inner-join.

Ross Leishman
Previous Topic: Mysterious Insufficient TEMP Tablespace...
Next Topic: Question about AWR
Goto Forum:
  


Current Time: Wed Dec 07 22:13:29 CST 2016

Total time taken to generate the page: 0.10705 seconds