ORA-54032 : column to be renamed is used in a virtual column expression

This is a simple note re-explaining what this 12cR1 ORA-54032 error is as it kicked in again a couple of days ago at one of my customer sites and showing that it has definitely been solved in 12cR2.

Here’s a how to reproduce it at will:

12cR1

SQL> select banner from v$version where rownum=1;

BANNER
----------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

SQL> create table t1(x number, y number, z number);

Table created.

SQL> alter table t1 rename column x to x_bis;

Table altered.

As you can see we renamed column x to x_bis without any issue. However if we create a column group extension out of (x_bis, y) columns we will not be able to rename neither x_bis nor y column as shown below:

SQL> SELECT
          dbms_stats.create_extended_stats
           (ownname   => user
           ,tabname   => 't1'
          ,extension => '(x_bis,y)'
           ) ext
    FROM dual;

-------------------------------
SYS_STU3L40_B_DWBMIA8PMJRGR$S0

SQL> alter table t1 rename column x_bis to x;
alter table t1 rename column x_bis to x
                             *
ERROR at line 1:
ORA-54032: column to be renamed is used in a virtual column expression

That’s exactly what happened to my client with the sole difference that the client has never explicitly created any virtual column hence its great stupefaction. I had to explain him that this is because Oracle has created, behind the scenes, an extended column group, which is nothing else than a virtual column. This creation is very probably a response to a SQL Plan Directive request.

And indeed, the column group extension I have manually created above is a virtual column created out of a combination of x_bis and y column as clearly shown by the default value of this virtual column:

SQL> select 
         column_name
        ,data_default
        ,data_length
     from
       user_tab_cols
     where
       table_name = 'T1'
     and hidden_column = 'YES';

COLUMN_NAME                    DATA_DEFAULT                      DATA_LENGTH
------------------------------ --------------------------------- -----------
SYS_STU3L40_B_DWBMIA8PMJRGR$S0 SYS_OP_COMBINED_HASH("X_BIS","Y")   22

We are unable to rename the x_bis column to x because x_bis is used in the definition of the above virtual column. But that was before the arrival of 12cR2.

12cR2

Execute the same experiment in 12cR2 and you will realize that things have changed as the followings demonstrate:

SQL> select banner from v$version where rownum=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> create table t1(x number, y number, z number);

Table T1 created.

SQL> alter table t1 rename column x to x_bis;

Table T1 altered.

  SELECT
      dbms_stats.create_extended_stats
       (ownname   => user
       ,tabname   => 't1'
       ,extension => '(x_bis,y)'
       ) ext
  FROM dual;

EXT
-------------------------------
SYS_STU3L40_B_DWBMIA8PMJRGR$S0

SQL> alter table t1 rename column x_bis to x;

Table T1 altered.

As of 12cR2 we are not anymore annoyed by the error when altering a table column used in a virtual column expression. This is because Oracle will take the alter column from the table level and extend it to the virtual column where it can be found as shown below:

SQL> select 
         column_name
        ,data_default
        ,data_length
     from
       user_tab_cols
     where
       table_name = 'T1'
     and hidden_column = 'YES';

COLUMN_NAME                    DATA_DEFAULT                   DATA_LENGTH
------------------------------ ------------------------------ -----------
SYS_STU3L40_B_DWBMIA8PMJRGR$S0 SYS_OP_COMBINED_HASH("X","Y")           22

Spot how, in the data_default value of the generated column group extension, the new modified name of the column has been changed as well.

SUMMARY

Again you know that an automatic extended column group extension, which might be created on behalf of you, generates a border corner where you are not anymore able to rename any of the columns that composed the extend virtual column group. Fortunately as of Oracle 12cR2 Oracle extends the column renaming directly to the extended virtual column and doesn’t raise the ORA-054023 anymore.

12cR2 OR-Expansion

In contrast to what the title seems suggesting, the OR-Expansion is not a new 12cR2 transformation. It has existed before and has been nicely explained in this article by the Oracle optimizer group. However, let me invite you to observe with me the following simple demonstration:

create table t1 (n1 number, n2 number, c1 varchar2(10));
create index t1_idx1 on t1(n1, n2);
create index t1_idx2 on t1(n2);

explain plan for
select * 
from t1
where (n1 =1 or n2 = 42);

select * from table(dbms_xplan.display);

The above query has two different execution plans in 12cR1 and 12cR2 as shown below respectively:

-- 12cR1 : 12.1.0.2.0
---------------------------------------------------------------
| Id  | Operation                            | Name    | Rows  | 
----------------------------------------------------------------
|   0 | SELECT STATEMENT                     |         |     2 |
|   1 |  CONCATENATION                       |         |       | 
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1      |     1 |
|*  3 |    INDEX RANGE SCAN                  | T1_IDX2 |     1 |
|   4 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1      |     1 |
|*  5 |    INDEX RANGE SCAN                  | T1_IDX1 |     1 | 
----------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("N2"=42)
   5 - access("N1"=1)
       filter(LNNVL("N2"=42))

-- 12cR2 : 12.2.0.1.0
------------------------------------------------------------------------
| Id  | Operation                             | Name            | Rows  | 
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                 |     2 | 
|   1 |  VIEW                                 | VW_ORE_BA8ECEFB |     2 |   
|   2 |   UNION-ALL                           |                 |       |   
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T1              |     1 |   
|*  4 |     INDEX RANGE SCAN                  | T1_IDX1         |     1 | 
|*  5 |    TABLE ACCESS BY INDEX ROWID BATCHED| T1              |     1 | 
|*  6 |     INDEX RANGE SCAN                  | T1_IDX2         |     1 | 
-------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("N1"=1)
   5 - filter(LNNVL("N1"=1))
   6 – access("N2"=42)

As you can see starting from Oracle 12cR2 Oracle has enhanced the OR expansion from a simple concatenation operator to a slightly more sophisticated union-all operation. The new OR-Expansion has by now been given a new name recognizable in the execution plan via the word: VW_ORE_###.

But what performance added value this transformation is supposed to bring?

Hopefully one of my real life queries gave me the answer to the above question: in contrast to its old implementation, the new enhanced 12cR2 OR-Expansion can be combined with other transformations opening, as such, a new path to the Oracle optimizer that is simply impossible to happen with the pre-12cR2 OR-Expansion.

A picture being worth a thousand of words here’s below a simple demonstration of how such a double transformation can be combined (the model comes from the previous article):

--12cR1
explain plan for
select 
   t1.*
  ,t2.*
from
   t1
left outer join
   t2
on
  (t1.id1 = t2.product_t1
  and
     (t2.start_date <= date'2012-06-12' and t2.end_date >= date'2012-06-07'
        or (t2.id1 > 100)
     )
  );
-------------------------------------------------------
| Id  | Operation           | Name            | Rows  |
-------------------------------------------------------
|   0 | SELECT STATEMENT    |                 | 14949 |
|*  1 |  HASH JOIN OUTER    |                 | 14949 | 
|   2 |   TABLE ACCESS FULL | T1              | 10000 |
|   3 |   VIEW              | VW_DCL_C83A7ED5 |  9950 |
|*  4 |    TABLE ACCESS FULL| T2              |  9950 |
-------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."ID1"="ITEM_2"(+))
   4 - filter("T2"."ID1">100 OR "T2"."START_DATE"<=TO_DATE(' 2012-06-12 
    00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T2"."END_DATE">=TO_DATE(' 2012-06-07 
              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

If you have read my previous article you will recognize in the above execution plan the Decorrelated Lateral view transformation via its name at line 3 (VW_DCL_C83A7ED5). At my client site this transformation was systematically triggering a full table scan so that I was obliged to cancel it as explained in the corresponding article. I would have instead loved if Oracle had gone a step forward and or-expanded this VW_DCL_C83A7ED5 lateral view in order to eliminate the OR disjunctive predicate to open the index access path possibility. Unfortunately combining these two transformation seems to be impossible by then.

That was until the arrival of the 12cR2 and its new enhanced OR-Expansion.

-- 12cR2 : 12.2.0.1.0
explain plan for
select /*+ OR_EXPAND(@"SEL$6226B99A" (1) (2)) */
   t1.*
  ,t2.*
from
   t1
left outer join
   t2
on
  (t1.id1 = t2.product_t1
  and
     (t2.start_date <= date'2012-06-12' and t2.end_date >= date'2012-06-07'
        or (t2.id1 > 100)
     )
  );
---------------------------------------------------------
| Id  | Operation             | Name            | Rows  | 
---------------------------------------------------------
|   0 | SELECT STATEMENT      |                 | 14950 |
|*  1 |  HASH JOIN OUTER      |                 | 14950 | 
|   2 |   TABLE ACCESS FULL   | T1              | 10000 | 
|   3 |   VIEW                | VW_DCL_C83A7ED5 |  9951 |
|   4 |    VIEW               | VW_ORE_37EAC9F1 |  9951 | 
|   5 |     UNION-ALL         |                 |       | 
|*  6 |      TABLE ACCESS FULL| T2              |  9901 | 
|*  7 |      TABLE ACCESS FULL| T2              |    50 | 
---------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 1 - access("T1"."ID1"="ITEM_2"(+))
 6 - filter("T2"."ID1">100)
 7 - filter("T2"."START_DATE"<=TO_DATE(' 2012-06-12 00:00:00', 'syyyy-mm-dd 
    hh24:mi:ss') AND "T2"."END_DATE">=TO_DATE(' 2012-06-07 00:00:00', 'syyyy-mm-dd 
              hh24:mi:ss') AND LNNVL("T2"."ID1">100))

Spot now how the 12cR2 Or-Expansion(VW_ORE_37EAC9F1) has been combined with the 12cR1 Decorrelated Lateral view(VW_DCL_C83A7ED5) opening, as such, the possibility to visit t2 table via an index access path since the disjunctive or predicate has been removed from the refactored query (the index has not been used in my case but that’s only a question of cost).

Since the 12cR2 Or-Expansion is a cost based transformation (see below in the 10053 trace file) it happens that Oracle has decided to do not use it. This is why I used the hint OR_EXPAND in order to force it kicking in for the pedagogic purpose of this article.

In order to produce the above execution plan, Oracle has gone through the following SQL refactoring steps:

 --first it has OR-Expanded the t2 table query block and named it VW_ORE_37EAC9F1

(select 
   t2.id1        item_1,
   t2.product_t1 item_2,
   t2.start_date item_3,
   t2.end_date   item_4,
   t2.padding    item_5
from
   t2 t2
where t2.id1 >100
union all
select 
   t2.id1        item_1,
   t2.product_t1 item_2,
   t2.start_date item_3,
   t2.end_date   item_4,
   t2.padding    item_5
from
   t2 t2
where 
    t2.start_date <=to_date('2012-06-12 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
and t2.end_date   >=to_date('2012-06-07 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
and lnnvl(t2.id1 >100)  VW_ORE_37EAC9F1

 --it have then Decorrelated the OR-Expanded query block and named it VW_DCL_C83A7ED5 

((select 
   t2.id1        item_1,
   t2.product_t1 item_2,
   t2.start_date item_3,
   t2.end_date   item_4,
   t2.padding    item_5
from
   t2 t2
where t2.id1 >100
union all
select 
   t2.id1        item_1,
   t2.product_t1 item_2,
   t2.start_date item_3,
   t2.end_date   item_4,
   t2.padding    item_5
from
   t2 t2
where 
    t2.start_date <=to_date('2012-06-12 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
and t2.end_date   >=to_date('2012-06-07 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
and lnnvl(t2.id1 >100)
) VW_ORE_37EAC9F1
) VW_DCL_C83A7ED5

--and finally it joined the Decorrelated OR-Expanded t2 table query with t1 table
select
  t1.id1   id1,
  t1.flag1 flag1,
  t1.flag2 flag2,
  t1.n1    n1,
  t1.v1    v1,
  vw_dcl_c83a7Ed5.item_1_0 id1,
  vw_dcl_c83a7Ed5.item_2_1 product_t1,
  vw_dcl_c83a7Ed5.item_3_2 start_date,
  vw_dcl_c83a7Ed5.item_4_3 end_date,
  vw_dcl_c83a7Ed5.item_5_4 padding
from t1 t1,
     (select
        vw_ore_37eac9f1.item_1 item_1_0,
        vw_ore_37eac9f1.item_2 item_2_1,
        vw_ore_37eac9f1.item_3 item_3_2,
        vw_ore_37eac9f1.item_4 item_4_3,
        vw_ore_37eac9f1.item_4 item_5_4
      from
        (select 
           t2.id1        item_1,
           t2.product_t1 item_2,
           t2.start_date item_3,
           t2.end_date   item_4,
           t2.padding    item_5
         from
            t2 t2
         where t2.id1 >100
        union all
         select 
           t2.id1        item_1,
           t2.product_t1 item_2,
           t2.start_date item_3,
           t2.end_date   item_4,
           t2.padding    item_5
         from
           t2 t2
         where 
           t2.start_date <=to_date('2012-06-12 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
       and t2.end_date   >=to_date('2012-06-07 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
       and lnnvl(t2.id1 >100)
      )  vw_ore_37eac9f1
     ) vw_dcl_c83a7Ed5
where t1.id1 = vw_dcl_c83a7ed5.item_2_1(+);

The OR-Expansion appears first at the Legend section of the CBO 10053 trace file:

Legend
 The following abbreviations are used by optimizer trace
 CBQT  – cost-based query transformation
 ORE   - CBQT OR-Expansion
 

It can be disabled locally using the hint NO_OR_EXPAND:

explain plan for
select /*+ NO_OR_EXPAND(@"SEL$6226B99A" (1) (2)) */
   t1.*
  ,t2.*
from
   t1
left outer join
   t2
on
  (t1.id1 = t2.product_t1
  and
     (t2.start_date <= date'2012-06-12' and t2.end_date >= date'2012-06-07'
        or (t2.id1 > 100)
     )
  );

-------------------------------------------------------
| Id  | Operation           | Name            | Rows  |
-------------------------------------------------------
|   0 | SELECT STATEMENT    |                 | 14999 | 
|*  1 |  HASH JOIN OUTER    |                 | 14999 | 
|   2 |   TABLE ACCESS FULL | T1              | 10000 |  
|   3 |   VIEW              | VW_DCL_C83A7ED5 | 10000 | 
|*  4 |    TABLE ACCESS FULL| T2              | 10000 | 
-------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."ID1"="ITEM_2"(+))
   4 - filter("T2"."ID1">100 OR "T2"."START_DATE"<=TO_DATE(' 2012-06-12 
    00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T2"."END_DATE">=TO_DATE(' 2012-06-07 
              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

And globally via the hidden param _optimizer_cbqt_or_expansion which defaults to on:

SQL> alter system set "_optimizer_cbqt_or_expansion"= off;

explain plan for
select /*+ OR_EXPAND(@"SEL$6226B99A" (1) (2)) */
   t1.*
  ,t2.*
from
   t1
left outer join
   t2
on
  (t1.id1 = t2.product_t1
  and
     (t2.start_date <= date'2012-06-12' and t2.end_date >= date'2012-06-07'
        or (t2.id1 > 100)
     )
  );
  
  select * from table(dbms_xplan.display);
 --------------------------------------------------------
| Id  | Operation             | Name            | Rows  | 
---------------------------------------------------------
|   0 | SELECT STATEMENT      |                 | 14999 |
|*  1 |  HASH JOIN RIGHT OUTER|                 | 14999 |
|   2 |   VIEW                | VW_DCL_C83A7ED5 | 10000 |
|*  3 |    TABLE ACCESS FULL  | T2              | 10000 |
|   4 |   TABLE ACCESS FULL   | T1              | 10000 |
---------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."ID1"="ITEM_2"(+))
   3 - filter("T2"."ID1">100 OR "T2"."START_DATE"<=TO_DATE(' 2012-06-12 
    00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T2"."END_DATE">=TO_DATE(' 2012-06-07 
              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

SUMMARY

Starting from Oracle 12cR2 the OR-Expansion has been enhanced from the classical concatenation to a more flexible union-all operation. This article shows that one of the advantages brought by this enhancement resides in the possibility it offers to the CBO to combine the new OR-Expansion with other transformations like with the Decorrelated Lateral view. This might increase the chance to have optimal execution plan in queries using disjunctive predicates.

Optimiser non sharing reasons

Whenever Oracle is prevented from sharing an execution plan of an already seen parent cursor, it hard parses a new plan and externalizes the corresponding non-sharing reason in the gv$sql_shared_cursor dedicated view. As of Oracle 12cR2 there are as many as 66 non-sharing reasons of which I have already explained 10 in the following series of articles I wrote for Toad World:

• Part 1  :hash_match_failed and load_optimizer_stats
• Part 2 : roll_invalid_mismatch and top_level_rpi_cursor
• Part 3 : bind_mismatch and stb_object_mismatch
• Part 4 : pq_slave_mismatch and optimizer_mismatch
• Part 5 : language_mismatch and auth_check_mismatch
• Part 6 :
• Part 7 :
• Part 8 :
• Part 9 :

_optimizer_ansi_join_lateral_enhance

There was a question raised at my client site shorter after I troubleshooted a performance issue due to the new 12cR1 Decorrelated Lateral View transformation: ”Is there a way to disable this CBO transformation without sending back the optimizer to its previous version?”

It ‘s an interesting question which has triggered a brief discussion and this article.

My short answer was: I have to test and check

And the long answer is:

10053 to the rescue

As long as I was able to engineer a reproducible case where this CBO transformation is used, I decided to generate its corresponding 10053 trace file and look for the word “lateral” in this file. I found 14 hits of this word of which one seems to correlate very well with my aim:

 *************************************
  PARAMETERS WITH DEFAULT VALUES
  ******************************
Compilation Environment Dump
optimizer_mode_hinted               = false
optimizer_features_hinted           = 0.0.0
parallel_execution_enabled          = true
parallel_query_forced_dop           = 0
parallel_dml_forced_dop             = 0
parallel_ddl_forced_degree          = 0
parallel_ddl_forced_instances       = 0
_query_rewrite_fudge                = 90
optimizer_features_enable           = 12.1.0.1
_optimizer_ansi_join_lateral_enhance = true --> this one

Although there is nothing in the name of the above parameter suggesting a decorrelation of a lateral view, it is nevertheless, the unique CBO parameter whith a name including the word lateral. This suggests that setting it off will cancel this transformation. Let’s then implement this transformation and try cancelling it with setting this parameter off.

SQL> select banner from v$version where rownum =1;

BANNER
-----------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

create table t1
as
 with generator as (
   select --+ materialize
      rownum id
   from dual
   connect by level <= 1000
)
select
    rownum id1,
    mod(rownum-1,2) flag1,
    mod(rownum-1,3) flag2,
    rownum          n1,
    lpad(rownum,30) v1
from
   generator v1,
   generator v2
where
   rownum <= 1e4;
    
alter table t1 add constraint t1_pk primary key (id1);
 
create table t2
as
 select
    level id1
   ,trunc((rownum+2)/2) product_t1
   ,date '2012-06-07' + mod((level-1)*5,10) + interval '5' minute start_date
   ,date '2012-06-08' + mod((level-1)*5,10) + interval '5' minute end_date
   ,rpad('xx',10) padding
from
   dual
connect by level <=1e4;
 
alter table t2 add constraint t2_pk primary key (id1);
alter table t2 add constraint t2_t1_fk foreign key (product_t1) references t1(id1);
 
-- creating an index covering the FK deadlock threat 
create index idx_t2_usr_1 on t2(product_t1, start_date);

explain plan for 
select 
   t1.*
  ,t2.*
from
   t1
left outer join
   t2
on
  (t1.id1 = t2.product_t1
  and
     (t2.start_date <= :1 and t2.end_date >= :2
        or (t2.id1 > 100)
     )
  );
select * from table(dbms_xplan.display(format=>'advanced'));

-------------------------------------------------------
| Id  | Operation           | Name            | Rows  |
-------------------------------------------------------
|   0 | SELECT STATEMENT    |                 | 14925 |
|*  1 |  HASH JOIN OUTER    |                 | 14925 |
|   2 |   TABLE ACCESS FULL | T1              | 10000 |
|   3 |   VIEW              | VW_DCL_1B0973D4 |  9926 |
|*  4 |    TABLE ACCESS FULL| T2              |  9926 |
-------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$A7519627
   2 - SEL$A7519627 / T1@SEL$1
   3 - SEL$6226B99A / VW_LAT_AE9E49E8@SEL$AE9E49E8
   4 - SEL$6226B99A / T2@SEL$1


Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$6226B99A" "T2"@"SEL$1")
      USE_HASH(@"SEL$A7519627" "VW_LAT_AE9E49E8"@"SEL$AE9E49E8")
      LEADING(@"SEL$A7519627" "T1"@"SEL$1" "VW_LAT_AE9E49E8"@"SEL$AE9E49E8")
      NO_ACCESS(@"SEL$A7519627" "VW_LAT_AE9E49E8"@"SEL$AE9E49E8")
      FULL(@"SEL$A7519627" "T1"@"SEL$1")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$9EC647DD")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$AE9E49E8")
      DECORRELATE(@"SEL$BCD4421C")
      OUTLINE(@"SEL$6226B99A")
      MERGE(@"SEL$9EC647DD")
      OUTLINE(@"SEL$0E991E55")
      OUTLINE(@"SEL$BCD4421C")
      DECORRELATE(@"SEL$6226B99A")
      OUTLINE_LEAF(@"SEL$A7519627")
      DECORRELATE(@"SEL$BCD4421C")
      OUTLINE_LEAF(@"SEL$6226B99A")
      ALL_ROWS
      DB_VERSION('12.1.0.1')
      OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."ID1"="ITEM_2"(+))
   4 - filter("T2"."ID1">100 OR "T2"."START_DATE"<=:1 AND "T2"."END_DATE">=:2)

Notice the apparition of view operation (VW_DCL_1B0973D4) at line n°3 in the above execution plan. This is Oracle way to signal the presence of a decorrelated lateral view transformation. If you want to learn more about this new transformation then you can read this US patent

DECORRELATE and NO_DECORRELATE hint

Before we go on, I want to show you very quickly how we can locally disable this new transformation using the following hint:

NO_DECORRELATE(@"SEL$BCD4421C")

explain plan for  
select /*+ NO_DECORRELATE(@"SEL$BCD4421C") */
   t1.*
  ,t2.*
from
   t1
left outer join
   t2
on
  (t1.id1 = t2.product_t1
  and
     (t2.start_date <= :1 and t2.end_date >= :2
        or (t2.id1 > 100)
     )
  );

select * from table(dbms_xplan.display);

-------------------------------------------------------------------------
| Id  | Operation                             | Name            | Rows  |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                 | 10000 |
|   1 |  NESTED LOOPS OUTER                   |                 | 10000 |
|   2 |   TABLE ACCESS FULL                   | T1              | 10000 |
|   3 |   VIEW                                | VW_LAT_1B0973D4 |     1 |
|*  4 |    TABLE ACCESS BY INDEX ROWID BATCHED| T2              |     1 |
|*  5 |     INDEX RANGE SCAN                  | IDX_T2_USR_1    |     1 |
-------------------------------------------------------------------------  

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("T2"."ID1">100 OR "T2"."START_DATE"<=:1 AND "T2"."END_DATE">=:2)
   5 - access("T1"."ID1"="T2"."PRODUCT_T1")

As you can see using the NO_DECORRELATE(@”SEL$BCD4421C”) hint we cancelled the VW_DCL_1B0973D4 decorrelated lateral view since we have now a simple VW_LAT_1B0973D4 lateral view

In passing it is important to note that the outline presented above contains three hints with the word DECORRELATE:

Outline Data
-------------
  /*+
      DECORRELATE(@"SEL$BCD4421C")
      DECORRELATE(@"SEL$6226B99A")
      DECORRELATE(@"SEL$BCD4421C")
  */

Of these three hints it is only the hint referencing the query block named @”SEL$BCD4421C” that will succeed to correlated/decorrelate the lateral view. I don’t know why this hint with that query block named is repeated two times. I don’t know also why using the other hint with the query block named @”SEL$6226B99A” doesn’t decorrelate the lateral view.

Looking back into the 10053 trace file I found the following lines for query blocks SEL$BCD4421C and SEL$6226B99A respectively:

DCL: Checking validity of lateral view decorrelation SEL$BCD4421C (#1)
DCL: Passed decorrelation validity for lateral view block SEL$BCD4421C (#1)
DCL: Decorrelation of lateral view query block SEL$BCD4421C (#1).
Registered qb: SEL$6226B99A 0x8e4a5b58 (VIEW DECORRELATED SEL$BCD4421C; SEL$BCD4421C)

This tends to suggest that SEL$6226B99A represents the query block name of the lateral view and that SEL$BCD4421C represents the query block name of the decorrelated lateral view. Simply put I think that when confronted to such a kind of situation where you want to locally cancel the lateral decorrelation view than use the hint NO_DECORRELATE with the query block name that exists in the outline and doesn’t exist in the query block name given by the dbms_xplan.display_cursor:

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$A7519627
   2 - SEL$A7519627 / T1@SEL$1
   3 - SEL$6226B99A / VW_LAT_AE9E49E8@SEL$AE9E49E8
   4 - SEL$6226B99A / T2@SEL$1

Don’t ask me why not simply using the table aliases as they appear in the original query to cancel this transformation. All my attempts have been unsuccessful.

Hidden parameter

What exactly is the parameter driving this transformation?

alter session set "_optimizer_ansi_join_lateral_enhance"=false;

explain plan for 
select 
   t1.*
  ,t2.*
from
   t1
left outer join
   t2
on
  (t1.id1 = t2.product_t1
  and
     (t2.start_date <= :1 and t2.end_date >= :2
        or (t2.id1 > 100)
     )
  );

select * from table(dbms_xplan.display(format =>'+outline'));
-------------------------------------------------------------------------
| Id  | Operation                             | Name            | Rows  |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                 | 10000 |
|   1 |  NESTED LOOPS OUTER                   |                 | 10000 |
|   2 |   TABLE ACCESS FULL                   | T1              | 10000 |
|   3 |   VIEW                                | VW_LAT_1B0973D4 |     1 |
|*  4 |    TABLE ACCESS BY INDEX ROWID BATCHED| T2              |     1 |
|*  5 |     INDEX RANGE SCAN                  | IDX_T2_USR_1    |     1 |
-------------------------------------------------------------------------

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$BCD4421C" "T2"@"SEL$1")
      INDEX_RS_ASC(@"SEL$BCD4421C" "T2"@"SEL$1" ("T2"."PRODUCT_T1" "T2"."START_DATE"))
      USE_NL(@"SEL$0E991E55" "VW_LAT_AE9E49E8"@"SEL$AE9E49E8")
      LEADING(@"SEL$0E991E55" "T1"@"SEL$1" "VW_LAT_AE9E49E8"@"SEL$AE9E49E8")
      NO_ACCESS(@"SEL$0E991E55" "VW_LAT_AE9E49E8"@"SEL$AE9E49E8")
      FULL(@"SEL$0E991E55" "T1"@"SEL$1")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$9EC647DD")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$AE9E49E8")
      MERGE(@"SEL$9EC647DD")
      OUTLINE_LEAF(@"SEL$0E991E55")
      OUTLINE_LEAF(@"SEL$BCD4421C")
      ALL_ROWS
      OPT_PARAM('_optimizer_ansi_join_lateral_enhance' 'false')
      DB_VERSION('12.1.0.1')
      OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("T2"."ID1">100 OR "T2"."START_DATE"<=:1 AND "T2"."END_DATE">=:2)
   5 – access("T1"."ID1"="T2"."PRODUCT_T1")

Notice again how we have succeeded to cancel the decorrelated lateral view transformation at a global level by setting off the hidden parameter _optimizer_ansi_join_lateral_enhance.

SUMMARY
In summary, does a decorrelated lateral view causes always a performance issue? No, not necessarily always. However if you fill in need of disabling it then you can:

• Use the corresponding no_decorrelate hint with the correct query block name for a local effect
• Set off the _optimizer_ansi_join_lateral_enhance parameter for a global effect with the usual warning when changing a hidden parameter.