This is a short note about a limitation in complex view merging for outer joins.

We start with two simple tables, t1 and t2. To show the effect we don’t even need to load any data.

create table t1 (
    id1 number not null
  , vc1 varchar2(200)
);

create table t2 (
    id1 number not null
  , id2 number not null
  , num1 number
);

I know, I said it’s about outer joins but let’s first check the execution plan for the INNER JOIN.

explain plan for
select *
from t1
  inner join (
    select /*+ merge */
        id2
      , 0 x
      , sum(num1) sum_num1
    from t2
    group by id2
  ) s1 on (s1.id2 = t1.id1)
;

select * from dbms_xplan.display();

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |   153 |     5  (20)| 00:00:01 |
|   1 |  HASH GROUP BY      |      |     1 |   153 |     5  (20)| 00:00:01 |
|*  2 |   HASH JOIN         |      |     1 |   153 |     4   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T1   |     1 |   127 |     2   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T2   |     1 |    26 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

Oracle merges the inline view as instructed by the MERGE hint. Btw., I’m only using the hint for demonstration purposes.

What happens if we change the join to a LEFT OUTER JOIN?

explain plan for
select *
from t1
  left outer join (
    select /*+ merge */
        id2
      , 0 x
      , sum(num1) sum_num1
    from t2
    group by id2
  ) s1 on (s1.id2 = t1.id1)
;
select * from dbms_xplan.display();

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |   143 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN OUTER     |      |     1 |   143 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | T1   |     1 |   115 |     2   (0)| 00:00:01 |
|   3 |   VIEW               |      |     1 |    28 |     3  (34)| 00:00:01 |
|   4 |    HASH GROUP BY     |      |     1 |    26 |     3  (34)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| T2   |     1 |    26 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

The inline view is not merged anymore. The optimizer trace reveals why it cannot merge the view anymore:

CVM:   Checking validity of merging in query block SEL$2 (#2)
CVM:     CVM bypassed: View on right side of outer join contains view with illegal column.
CVM:     CVM bypassed: Externally referenced expressions are not merge-safe.
CVM:     CVM bypassed: view on right side of Outer Join + MuLTiple TABle.
CVM:     CVM bypassed: view on right side of Outer Join + MuLTiple TABle.

In case you haven’t noticed, there’s this little expression in the projection of the inner SELECT on line 7 (“0 x”). As soon as we remove it, the view will be merged by the optimizer also for LEFT OUTER JOIN.

explain plan for
select *
from t1
  left outer join (
    select /*+ merge */
        id2
--    , 0 x
      , sum(num1) sum_num1
    from t2
    group by id2
  ) s1 on (s1.id2 = t1.id1)
;
select * from dbms_xplan.display();

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |   153 |     5  (20)| 00:00:01 |
|   1 |  HASH GROUP BY      |      |     1 |   153 |     5  (20)| 00:00:01 |
|*  2 |   HASH JOIN OUTER   |      |     1 |   153 |     4   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T1   |     1 |   127 |     2   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T2   |     1 |    26 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

Thanks to point and click tools *cough* Cognos *cough* I’ve seen this a lot lately 😉

Footnote: tests run on 12.2.0.1