Query Featuring Outer Joins Behaves Differently in Oracle 12C

Query featuring outer joins behaves differently in Oracle 12c

UPDATE: This is fixed in 12.1.0.2.

---

This definitely looks like a bug in 12.1.0.1. I would encourage you to create a service request through Oracle support. They might be able to find a fix or a better work around. And hopefully Oracle can fix it in a future version for everyone. Normally the worst part about working with support is reproducing the issue. But since you already have a very good test case this issue may be easy to resolve.

There are probably many ways to work around this bug. But it's difficult to tell which method will always work. Your query re-write may work now, but if optimizer statistics change perhaps the plan will change back in the future.

Another option that works for me on 12.1.0.1.0 is:

ALTER SESSION SET optimizer_features_enable='11.2.0.3';

But you'd need to remember to always change this setting before the query is run, and then change it back to '12.1.0.1' after. There are ways to embed that within a query hint, such as /*+ OPT_PARAM('optimizer_features_enable' '11.2.0.3') */. But for some reason that does not work here. Or perhaps you can temporarily set that for the entire system and change it back after a fix or better work around is available.

Whichever solution you use, remember to document it. If a query looks odd the next developer may try to "fix" it and hit the same problem.

Performance issues with outer joins to view in Oracle 12c

We discovered the cause of the performance issue. The following 2 system parameters were changed at the system level by the DBAs for the main application that uses our client's Oracle server:

_optimizer_cost_based_transformation = OFF 
_optimizer_reduce_groupby_key = FALSE

When we changed them at the session level to the following, the query above that joins the 2 views returns results in less than 2 seconds:

alter session set "_optimizer_cost_based_transformation"=LINEAR;
alter session set "_optimizer_reduce_groupby_key" = TRUE;
COMMIT;

Changing this parameter did not have an impact on performance:

alter session set optimizer_adaptive_features=FALSE;
COMMIT;

We also found that changing the following parameter improved performance even more for our more complex views:

alter session set optimizer_features_enable="11.2.0.3";
COMMIT;

Column outer joined in subquery (ORA-01799)

You may use CTE with row_number to classify the row with the minimal REV_ID and the outer join only to those rows.

The advantage is this approach works even if you want some other attributes from T1

with t1_min as
(select REC_ID, REV_ID,
row_number() over (partition by REC_ID order by  REV_ID) as rn
from TABLE_1)
SELECT t2.rec_id t2_rec_id,t1.rec_id t1_rec_id,
t1.rev_id
from TABLE_2 T2
LEFT OUTER JOIN t1_min T1 on T1.REC_ID = T2.REC_ID 
and t1.rn = 1

 T2_REC_ID  T1_REC_ID     REV_ID
---------- ---------- ----------
         1          1         23
         2          2         19

What is the difference between INNER JOIN and OUTER JOIN?

Assuming you're joining on columns with no duplicates, which is a very common case:

• An inner join of A and B gives the result of A intersect B, i.e. the inner part of a Venn diagram intersection.

• An outer join of A and B gives the results of A union B, i.e. the outer parts of a Venn diagram union.

Examples

Suppose you have two tables, with a single column each, and data as follows:

A    B
-    -
1    3
2    4
3    5
4    6

Note that (1,2) are unique to A, (3,4) are common, and (5,6) are unique to B.

Inner join

An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the two rows they have in common.

select * from a INNER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b;

a | b
--+--
3 | 3
4 | 4

Left outer join

A left outer join will give all rows in A, plus any common rows in B.

select * from a LEFT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b(+);

a |  b
--+-----
1 | null
2 | null
3 |    3
4 |    4

Right outer join

A right outer join will give all rows in B, plus any common rows in A.

select * from a RIGHT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a(+) = b.b;

a    |  b
-----+----
3    |  3
4    |  4
null |  5
null |  6

Full outer join

A full outer join will give you the union of A and B, i.e. all the rows in A and all the rows in B. If something in A doesn't have a corresponding datum in B, then the B portion is null, and vice versa.

select * from a FULL OUTER JOIN b on a.a = b.b;

 a   |  b
-----+-----
   1 | null
   2 | null
   3 |    3
   4 |    4
null |    6
null |    5

---

Related Topics