We are doing a large ETL load from Oracle 12c. Every now and then Oracle optimizer decides to create a new execution plan for a SQL statement that has previously been running just fine. Usually this results in a 10x performance degradation. When I ask the client DBAs and Oracle consultants (client is a Gold partner), they usually ask us to convert ANSI joins to traditional Oracle joins. And usually this has helped. My question is, is the poor support for ANSI joins acknowledged by Oracle? Does Oracle recommend using traditional joins? (I've read Oracle books stating the exact opposite.)
8
-
2Oracle recommends (at least in one point in their docs) the contrary: to use ANSI joins. Not sure if there are any cases where the old proprietary joins have still any performance benefits. They did have when the ANSI joins were first added but I would hope that has been fixed by now.– ypercubeᵀᴹCommented Sep 22, 2017 at 12:50
-
@ypercubeᵀᴹ Would you happen to have a link to the docs?– RubioCommented Sep 22, 2017 at 12:54
-
From Oracle 11 docs, Joins: "Oracle recommends that you use the FROM clause OUTER JOIN syntax rather than the Oracle join operator. Outer join queries that use the Oracle join operator (+) are subject to the following rules and restrictions, which do not apply to the FROM clause OUTER JOIN syntax: ..."– ypercubeᵀᴹCommented Sep 22, 2017 at 13:02
-
@ypercubeᵀᴹ Thanks. Stricly speaking that recommendation applies to outer joins. This is interesting, though. I've been told by Oracle consultants that the SQL parser first has to convert an ANSI join to an Oracle join and then parse the statement. And if this conversion produces a less than perfect result, the final execution plan might be totally wrong.– RubioCommented Sep 22, 2017 at 13:12
-
Yes, the FROM clause is converted to an internal format. If all the joins are inner though, I highly doubt that this is the reason for the different performance. It's probably just because you rewrote query. In theory - and in practice - even changing the order that the tables are placed in the FROM clause may produce different execution plans- even though the two queries are 100% equivalent. This is because the optimizer/planner can only use a small time to decide a plan and the possibilities for a complex query, with 5+ joins are in the trillions of possible plans.– ypercubeᵀᴹCommented Sep 22, 2017 at 13:18
|
Show 3 more comments
1 Answer
This question has been answered by Chris Saxon of Oracle's "Ask TOM" team, who explains why ANSI joins are preferable to the old Oracle syntax:
This is because there are number of restrictions that apply to the (+) operator, but not the "outer join" clause. Follow the link for the full list.
The article does acknowledge that there can be cases where the old Oracle syntax is necessary (Materialized Views don't support ANSI - should be "fixed" in his view - implying it is a bug).
The documentation link referred to in the article says:
Oracle recommends that you use the FROM clause OUTER JOIN syntax rather than the Oracle join operator. Outer join queries that use the Oracle join operator (+) are subject to the following rules and restrictions, which do not apply to the FROM clause OUTER JOIN syntax:
(rules excluded - follow link)
In your case, you might want to take a careful look at Plan Management (previously called Plan Stability - nice Oracle white paper here). The site (oracle-base) is a superb site for all things Oracle - I'd put it in my top 5 no matter what I was doing (dev, test, prod...).
The big plus is you won't get major swings in performance as a plan tilts from one optimization to the other - the price you pay for this is that you won't see performance improvements either! If you do implement plan management, then you should revisit those managed plans (on dev/test) from time to time to ensure that they are still optimal (or at least satisfactory).
-
both Oracle Manual and Tom Kyte refer to outer joins when they recommend to use ansi joins. Commented Sep 22, 2017 at 19:00
-
the important part of the answer is the recommendation of SQL plan management Commented Sep 22, 2017 at 19:04
-
1In the light of your comment, I have clarified slightly -see my most recent edit. I never said that Oracle and/or Tom Kyte didn't refer to outer joins. The "old" Oracle syntax for inner joins was simply the old syntax for everyone before ANSI standardised it (where t1.joining_field = t2.joining_field vs. the
JOINkeyword)!– VéraceCommented Sep 22, 2017 at 19:12 -
What Verace said above. When you say "traditional Oracle joins", we understend the proprietary old Oracle syntax for outer joins. The old syntax for inner/cross joins, with the comma operator and the conditoins in WHERE, is ANSI very well, not Oracle. It's just older ANSI (89 or 86, not sure). The JOIN syntax was introduced in ANSI/ISO 92. Commented Sep 22, 2017 at 19:49
-
@ypercubeᵀᴹ - you mean there were other ways of joining tables before
WHERE t1.joining_field(s) = t2.joining_field(s)? Just out of curiosity, what were they? Oh, BTW, your point about the multiplicity of possibilities for joins is well made!– VéraceCommented Sep 22, 2017 at 20:05