Home » SQL & PL/SQL » SQL & PL/SQL » Outer Join Syntax and Performance?
Outer Join Syntax and Performance? [message #218952] Mon, 12 February 2007 03:47 Go to next message
rsoma
Messages: 15
Registered: April 2005
Location: Chennai, India
Junior Member
Is there is any performance hit by using ANSI Join with (+) join.

We are having performance issue if we use ANSI Join, The results is coming faster wehre (+) Join. We are using Oracle 10g.

Thanks in Advance.
Re: Outer Join Syntax and Performance? [message #218998 is a reply to message #218952] Mon, 12 February 2007 08:03 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Double check (and prove with a demo) that your two statements are indeed doing exactly the same thing logically - that they will return exactly the same results.
Re: Outer Join Syntax and Performance? [message #219085 is a reply to message #218998] Mon, 12 February 2007 20:23 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
There is no difference in the performance, but there are also no guarantees that Oracle will choose the same plan. Explain plan both and then try to tune the ANSI version to use the same plan as the (+) version.

Ross Leishman
Re: Outer Join Syntax and Performance? [message #225304 is a reply to message #218952] Mon, 19 March 2007 11:29 Go to previous messageGo to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

Is that explain plan gives less cost if we use (+) version rather than ANSI version. To be honest, i havent used ANSI till now...

Naveen
Re: Outer Join Syntax and Performance? [message #225306 is a reply to message #225304] Mon, 19 March 2007 11:35 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You cannot (just) compare costs of two different statements, especially on lower versions.
Cost reflects the relative cost of that execution plan compared to other execution plans for the same statement.
Then again, on 10G, I think it becomes more and more an absolute value..
Previous Topic: Need the solution for the following SQL giving 00918 error(Merged)
Next Topic: Frustrating Requisition Function
Goto Forum:
  


Current Time: Sun Dec 11 00:26:14 CST 2016

Total time taken to generate the page: 0.11159 seconds