Home » SQL & PL/SQL » SQL & PL/SQL » Join Style Performance
Join Style Performance [message #194941] Tue, 26 September 2006 04:56 Go to next message
AbuShreek
Messages: 22
Registered: May 2006
Junior Member
Hello,

Is there any difference in terms of performance between the old and new styles of inner JOINs?

Old Style:
SELECT * FROM A, B
WHERE A.Col1 = B.Col1

New Style:
SELECT * FROM A INNER JOIN B
ON A.Col1 = B.Col2

Any advantages or disadvantages here or there or is it just a matter of coding style?

Thanks in advance.

Re: Join Style Performance [message #195082 is a reply to message #194941] Tue, 26 September 2006 23:15 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Absolutely no difference in performance. Mostly it's just coding style, and conformance with ANSI.

There are still some "undocumented features" with the ANSI join syntax in 10.1 (sorry, I haven't tried 10.2; and I don't know whether they persist from 9i, or were simply introduced in 10.1).

  • DRIVING_SITE hint is ignored if local and remote tables are joined with ANSI syntax. Fixed in 10.2 I suspect.
  • ANSI syntax join views (mostly) do not support Global View Hints. ie. When you SELECT from an ANSI join view, it is very, very hard to provide hints on the operations inside the views.
  • Materialized Views using ANSI syntax are deemed "complex", and therefore do not support Fast Refresh.


None of these are good reasons not to use the ANSI syntax wherever possible. Ditch the old syntax; especially outer joins (+) - its only advantage these days is to make Oracle developers feel superior when other RDBMS developers can't read their code.


Ross Leishman
Re: Join Style Performance [message #195183 is a reply to message #195082] Wed, 27 September 2006 05:53 Go to previous message
AbuShreek
Messages: 22
Registered: May 2006
Junior Member
Thanks alot.
Previous Topic: Select Statement
Next Topic: Unable to access DBMS_PIPE from a procedure
Goto Forum:
  


Current Time: Sat Dec 03 20:28:01 CST 2016

Total time taken to generate the page: 0.27342 seconds