_with_subquery parameter – controlling subquery factoring

It seems that Oracle 11g added new hidden parameter – _with_subquery.

In older versions, we have 2 hints to control subquery factoring.

• MATERIALIZE hint: Materialize the with subquery
• INLINE hint: Do not materilize the with subquery

With _with_subquery parameter, we can control the subquery factoring in session and/or system level, like following:

alter session set "_with_subquery" = materialize;

Possible values are

• optimizer: Let the optimizer choose what it likes – the default mode
• materialize: Always materialize
• inline: Always inline

This parameter is especially useful when you have no right to modify the SQL itself, but want to change the behavior(materialize or not). Stored outline would be one of the solutions, but not elegant.

Following is a meanigless, but very clear test case.

select * from v$version;

BANNER                                                                  
-------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production  
PL/SQL Release 11.1.0.6.0 - Production                                  
CORE    11.1.0.6.0    Production                                            
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production                 
NLSRTL Version 11.1.0.6.0 - Production                                   

explain plan for
with x as (select * from t1),
  y as (select * from t2)
select *
from x, y
where x.c1 = y.c1
;
-- Inlined!
-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|*  1 |  HASH JOIN         |      |
|   2 |   TABLE ACCESS FULL| T2   |
|   3 |   TABLE ACCESS FULL| T1   |
-----------------------------------

explain plan for
with x as (select /*+ materialize */ * from t1),
  y as (select /*+ materialize */ * from t2)
select *
from x, y
where x.c1 = y.c1
;
-- materialize hint will materialize the subquery
------------------------------------------------------------------
| Id  | Operation                  | Name                        |
------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                             |
|   1 |  TEMP TABLE TRANSFORMATION |                             |
|   2 |   LOAD AS SELECT           |                             |
|   3 |    TABLE ACCESS FULL       | T1                          |
|   4 |   LOAD AS SELECT           |                             |
|   5 |    TABLE ACCESS FULL       | T2                          |
|*  6 |   HASH JOIN                |                             |
|   7 |    VIEW                    |                             |
|   8 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D6623_22D161E4 |
|   9 |    VIEW                    |                             |
|  10 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D6624_22D161E4 |
------------------------------------------------------------------

-- Let's materialze all with-subqueries in my session!
alter session set "_with_subquery" = materialize;

explain plan for
with x as (select * from t1),
  y as (select * from t2)
select *
from x, y
where x.c1 = y.c1
;

------------------------------------------------------------------
| Id  | Operation                  | Name                        |
------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                             |
|   1 |  TEMP TABLE TRANSFORMATION |                             |
|   2 |   LOAD AS SELECT           |                             |
|   3 |    TABLE ACCESS FULL       | T1                          |
|   4 |   LOAD AS SELECT           |                             |
|   5 |    TABLE ACCESS FULL       | T2                          |
|*  6 |   HASH JOIN                |                             |
|   7 |    VIEW                    |                             |
|   8 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D6625_22D161E4 |
|   9 |    VIEW                    |                             |
|  10 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D6626_22D161E4 |
------------------------------------------------------------------