Good Morning,
We upgraded to 12.2. in our development environment in June and have been
extensively testing our code without encountering this issue. I ran this
by our developers and they supplied me with code containing a nested
sub-query using the left outer join. In the code we're using, the 'ON'
component of the join occurs after the the actual query portion:
left outer join ( select ar.iuser_oid
from assessment_result ar
join skill on skill.oid = ar.skill_oid
join dbstring on dbstring.oid = skill.module_name_oid and
mnemonic = 'ISIP_ARPM'
where ar.serial = 0 and
ar.period = :PERIOD {0}
having count(ar.oid) >=
:ARPMSKILLCOUNT
group by ar.iuser_oid
) arfiltered
on arfiltered.iuser_oid =
membership.oid
There is a similar example on stackoverflow at:
https://stackoverflow.com/questions/14571254/ora-01799-a-column-may-not-be-outer-joined-to-a-subquery
It seems that if the 'ON' clause is self-contained, the error does not
appear. If I re-write our code to:
left outer join assessment_result ar
on
(subquery)
the query returns the error code.
Just thought it was interesting.
Thanks for the heads-up!
On Mon, Aug 13, 2018 at 10:50 AM, Redacted sender Jay.Miller for DMARC <
dmarc-noreply@xxxxxxxxxxxxx> wrote:
The sql_id never shows up in v$sql despite displaying in OEM while it runs
which makes things more difficult. In hindsight was a clue that something
strange was going on. It started happening as soon as we migrated from 12.1
to 12.2.
I’m working on trimming the SQL down, so far I’ve eliminated about 60% of
it and am still able to reproduce the issue. Will keep working on it later
this afternoon. I’ll provide the EXPLAIN PLAN output once it’s small enough
that be easily legible.
Jay Miller
Sr. Oracle DBA
201.369.8355
*From:* Andy Sayer [mailto:andysayer@xxxxxxxxx]
*Sent:* Friday, August 10, 2018 6:58 PM
*To:* Miller, Jay
*Cc:* dmarc-noreply@xxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
*Subject:* Re: 12.2 bug for ANSI outer join syntax
Sounds like a good path to try.
Explain plan might be reliable enough to point out the issue, otherwise
you could search for the appropriate sql_id in v$sql and call
dbms_xplan.display_cursor with the sql_id explicitly passed to it.
It’s possible your left join is conditional and you’re suffering from
something like what I talk about in
https://ctandrewsayer.wordpress.com/2018/06/06/conditional-outer-joins-
forcing-nested-loops/
<https://urldefense.proofpoint.com/v2/url?u=https-3A__ctandrewsayer.wordpress.com_2018_06_06_conditional-2Douter-2Djoins-2Dforcing-2Dnested-2Dloops_&d=DwMFaQ&c=nulvIAQnC0yOOjC0e0NVa8TOcyq9jNhjZ156R-JJU10&r=aiKV3Uv2Wo7GqYQcis9TSvB1MZslPOnintrOY1rjG58&m=wF2FXjoCVstv5FWb8tXTkNYblqCjZLiVty4VuSaEDXY&s=i8ylVFmrNk5ON3iPV-C-aFMwgraH2IvWuMSnH2x_a1w&e=>
I.e a join that looks like it ought to be done by a hash join is really
nested looped. It might be worse than that and the join predicate is not
being pushed all the way to act against the table (and take advantage of
indexes) But is going against the subquery as a non mergeable view instead.
If this is something that has only started happening then maybe there’s
some funny transformations occurring around lateral views. There were some
bugs surrounding them originally, it’s possible that they’ve been fixed but
at the cost of performance.
If you share how the join is performed in the plan (along with the
predicates section), then that could help an explanation.
On Fri, 10 Aug 2018 at 21:50, <Jay.Miller@xxxxxxxxxxxxxxxx> wrote:
I should have time to do some testing next week. I hope to prune the query
down quite a bit to make it simpler/more legible. I do have both execution
plans though I had use EXPLAIN PLAN to get the 12.2 one as the query never
completed. They are similar but not identical.
Fortunately since optimizer_features_enable is settable on the session
level it will be easy to test.
Jay Miller
Sr. Oracle DBA
201.369.8355
*From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@
freelists.org] *On Behalf Of *Andy Sayer
*Sent:* Friday, August 10, 2018 3:06 PM
*To:* dmarc-noreply@xxxxxxxxxxxxx
*Cc:* oracle-l@xxxxxxxxxxxxx
*Subject:* Re: 12.2 bug for ANSI outer join syntax
Left outer joins to subqueries are common, are you sure the scenario is as
simple as that?
Was there an execution plan? Perhaps the optimizer had correlated the
subquery whereas that needs to not happen for your query performance to be
acceptable (or vice versa).
Have you got an end-to-end demo you can share?
Regards,
Andy
