What is the exact difference between Oracle (+) and comma separate tables and join tables. Somebody force me to use oracle (+) instead of left join. Is their any performance issue ? Because as i aware of these notions they are identical.
1 Answer
Write both queries and run EXPLAIN PLAN on both of them. Comparing the plans will identify any performance differences.
From a development perspective, explicit joins offer several advantages over the older syntax.
By keeping the ON clauses together with the tables they reference, it's easier to see the join itself.
Separating out the ON clauses from the WHERE predicates make it easier to understand the developer's intention. Sometimes a spaghetti collection of tables might only have one WHERE predicate after all the join predicates are removed.
It's much harder to forget a predicate with the explicit syntax. If you forget a join predicate, you're going to return a cartesian product. Two tables with a million rows each will form a trillion-row cartesian product. If you know you've forgotten a join condition because your result set is suspicious, it's much easier to identify what's missing with the explicit syntax.
I will argue, against the advice of some longbeards, that even a large number of tables is easier to manage with explicit joins and that no query favors the implicit syntax for readability. Again, this is because the join predicates are kept next to their referenced tables. If you're limiting yourself to 80 columns of text, a long query could mean a lot of flipping up and down with the implicit syntax. If you think the typing is excessive, you should solve this with your text editor and not with sloppy syntax. (Multiline editing in Sublime Text is one solution, for example.)
(+)operator. Whoever is "forcing" you to use it, should read the Oracle manual.