Subquery unnesting that does not require costing

There are two types of subquery unnesting: the first one is merged by the CBO with its outer query and the second one is turned into an inline view before being, eventually, merged with its outer query. While the latter is cost based, the former is imperatively done provided it passes the subquery unnesting validity check. This is why, we may, sometimes, pay a performance penalty because of the automatic subquery unnesting.

Automatic subquery unnesting that does not require costing

This is precisely what I have experienced at one of my customer sites:

insert into t1_targ
     (id)
select
     t2_id
from
     t2 lt1
where
     t2.l_id > 0
and  t2.status in (0,1,3)
and  t2.class = 'XyyZgg'
and EXISTS
         (select null
          from t2 lt2
          where lt2.t1_id  = lt1.l_id
          and   lt2.status = 2
          and   lt2.class != 'XyyZgg'
          );
Global Stats
=========================================
| Elapsed |   Cpu   |  IO      | Buffer |
| Time(s) | Time(s) | Waits(s) |  Gets  |
=========================================
|    4070 |    343  |     3724 |   31M  |
=========================================

SQL Plan Monitoring Details (Plan Hash Value=1860787001)
===================================================================
| Id    |                 Operation                   |       Name |
|       |                                             |            |
===================================================================
|     0 | INSERT STATEMENT                            |            |
|     1 |   LOAD TABLE CONVENTIONAL                   | T1_ARG     |
|     2 |    HASH JOIN                                |            |
|     3 |     NESTED LOOPS                            |            |        
|     4 |      NESTED LOOPS                           |            |
|     5 |       STATISTICS COLLECTOR                  |            |
|     6 |        INLIST ITERATOR                      |            |
|     7 |         TABLE ACCESS BY INDEX ROWID BATCHED | T2         |
|     8 |          INDEX RANGE SCAN                   | IDX_T262   |
|     9 |       INDEX UNIQUE SCAN                     | PK_T250    |
|    10 |     TABLE ACCESS BY INDEX ROWID             | T2         |
|    11 |    TABLE ACCESS BY INDEX ROWID BATCHED      | T2         |
|    12 |     INDEX SKIP SCAN                         | IDX_T262   |
===================================================================

As you can see the EXISTS subquery has been unnested and merged into the body of the outer query to form a join as the following lines taken from the corresponding 10053 trace file prove:

*****************************
Cost-Based Subquery Unnesting
*****************************
SU: Unnesting query blocks in query block SEL$1 (#1) that are valid to unnest.
Subquery Unnesting on query block SEL$1 (#1)SU: Performing unnesting that does not require costing. -- automatic
SU: Considering subquery unnest on query block SEL$1 (#1).
SU:   Checking validity of unnesting subquery SEL$2 (#2)
SU:   Passed validity checks. -- subquery is valid for unnesting
SU:   Transforming EXISTS subquery to a join. -- unnesting is done here
Registered qb: SEL$5DA710D3 0x64c1aaf8 (SUBQUERY UNNEST SEL$1; SEL$2)

That is how the automatic subquery unnesting works: provided there are no restrictions pre-empting it, the unnesting will be automatically done.

if, I add a restriction (rownum <=10) into the above query, the unnesting will be bypassed as the following proves:

insert into t1_targ
     (id)
select
     t2_id
from
     t2 lt1
where
     t2.l_id > 0
and  t2.status in (0,1,3)
and  t2.class = 'XyyZgg';
and EXISTS
         (select null
          from t2 lt2
          where lt2.t1_id  = lt1.l_id
          and   lt2.status = 2
          and   lt2.class != 'XyyZgg';
          and   rownum <= 10 -- restriction
          );

===================================================================
| Id    |                 Operation                   |       Name |
===================================================================
|     0 | INSERT STATEMENT                            |            |
|     1 |   LOAD TABLE CONVENTIONAL                   | T1_ARG     |
|     2 |    INLIST ITERATOR                          |            |
|     3 |     TABLE ACCESS BY INDEX ROWID BATCHED     | T2         | 
|     4 |      INDEX RANGE SCAN                       | IDX_T262   |                       
|     5 |       COUNT STOPKEY                         |            |
|     6 |        TABLE ACCESS BY INDEX ROWID          | T2         |            
|     7 |         INDEX UNIQUE SCAN                   | PK_T250    |
===================================================================

*****************************
Cost-Based Subquery Unnesting
*****************************
SU: Unnesting query blocks in query block SEL$1 (#1) that are valid to unnest.
Subquery Unnesting on query block SEL$1 (#1)SU: Performing unnesting that does not require costing.
SU: Considering subquery unnest on query block SEL$1 (#1).
SU:   Checking validity of unnesting subquery SEL$2 (#2)
SU:     SU bypassed: Query block references ROWNUM. -- restriction
SU:   Validity checks failed.-- unnesting is bypassed

At my client site, however, automatically unnesting the subquery was a dramatic choice made by the CBO as the historical statistics of the corresponding insert/select displayed below clearly show (sometimes more than 14,000 seconds without completing):

@HistStats
Enter value for sql_id:6gryp77nfgtv8
Enter value for from_date: 15052018

SNAP_BEGIN                PLAN_HASH_VALUE EXECS END_OF_FETCH  AVG_ETIME  AVG_LIO  
------------------------- --------------- ----- ------------ ----------  --------
16-MAY-18 12.00.13.600 AM      1860787001     0            0      3607    2975544     
16-MAY-18 01.00.20.220 PM      1860787001     1            1      3062    2708725
16-MAY-18 02.00.41.960 PM      1860787001     0            0      2509    3374627
16-MAY-18 03.00.48.113 PM      1860787001     0            0      4169    4970849
16-MAY-18 04.00.54.446 PM      1860787001     0            0      7659   16659725
16-MAY-18 05.00.04.833 PM      1860787001     0            0     13342   21798355
16-MAY-18 06.00.14.538 PM      1860787001     0            0     14245   35578453
../...
17-MAY-18 08.00.59.398 PM      1860787001     0            0      3544    3627666

But immediately when I cancelled the unnesting (using /*+ no_unnest */) I got a better execution time as shown below:

@HistStats
Enter value for sql_id:6gryp77nfgtv8
Enter value for from_date: 17052018

SNAP_BEGIN                PLAN_HASH_VALUE EXECS END_OF_FETCH  AVG_ETIME  AVG_LIO  
------------------------- --------------- ----- ------------ ----------  --------
17-MAY-18 08.00.59.398 PM      1860787001     0            0     3544    3627666
18-MAY-18 11.00.30.847 AM          530894     2            2     116      528385  -- 116 sec/exec 

Cost based subquery unnesting

The subquery unnesting ceases, however, to be imperatively done whenever the subquery is transformed into an inline view. In this case, even when there is nothing preventing the subquery from being unnested, Oracle will evaluate the cost of unnesting it before proceeding to the transformation. Here’s below a typical illustration (the model is at the end of this blog post):

explain plan for
 select
     a.n1
 from
    t1 a
where
    a.n1 = 10
and exists
    (select 
	    null 
	 from
	    t1 b
	   ,t2 c
	 where
	    b.n1 = c.n1
	 and 
	    b.n2 = a.n3
    );

select * from table(dbms_xplan.display);

----------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes |
----------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |     9 |
|   1 |  NESTED LOOPS SEMI           |         |     1 |     9 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1      |     1 |     7 |
|*  3 |    INDEX UNIQUE SCAN         | T1_PK   |     1 |       |
|   4 |   VIEW PUSHED PREDICATE      | VW_SQ_1 |     1 |     2 | -- subquery into view
|   5 |    NESTED LOOPS SEMI         |         |     1 |    11 |
|*  6 |     TABLE ACCESS FULL        | T1      |     3 |    21 |
|*  7 |     INDEX UNIQUE SCAN        | T2_PK   |   300 |  1200 |
----------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("A"."N1"=10)
   6 - filter("B"."N2"="A"."N3")
   7 – access("B"."N1"="C"."N1")

*****************************
Cost-Based Subquery Unnesting
*****************************
SU: Unnesting query blocks in query block SEL$1 (#1) that are valid to unnest.
Subquery Unnesting on query block SEL$1 (#1)SU: Performing unnesting that does not require costing.
SU: Considering subquery unnest on query block SEL$1 (#1).
SU:   Checking validity of unnesting subquery SEL$2 (#2)
SU:   Passed validity checks, but requires costing.  -- here where the CBO is costing the subquery unnesting
SU: Using search type: exhaustive
SU: Starting iteration 1, state space = (2) : (1)
SU:   Unnesting subquery query block SEL$2 (#2)Registered qb: SEL$683B0107 0x70409408 (SUBQ INTO VIEW FOR COMPLEX UNNEST SEL$2)

Summary

Don’t be surprised to see Oracle, sometimes, using a dramatic execution plan when it unnests a subquery. This might be related to the automatic unnesting which does not require costing. It is done by Oracle provided the subquery passes the unnesting validity check and is not transformed into an inline view prior to its unnesting.

create table t1
as select
    rownum n1
   ,trunc((rownum-1)/3) n2
   ,mod(rownum,10) n3
from dual
connect by level <= 3e2;

create table t2
as select
    2*rownum n1
   ,trunc(rownum,5) n2
   ,lpad('x',5) vc
from dual
connect by level <= 3e2;

alter table t1 add constraint t1_pk primary key (n1);
alter table t2 add constraint t2_pk primary key (n1);