Dion Cho – Oracle Performance Storyteller

We are natural born scientists

_with_subquery parameter – controlling subquery factoring

with 2 comments

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 |
------------------------------------------------------------------
About these ads

Written by Dion Cho

February 9, 2009 at 7:33 am

2 Responses

Subscribe to comments with RSS.

  1. Mr.Cho I am very happy to see that you are now blogging in English, I have read your posts here and they all are very handy.

    Thank you very much for your efforts, I will be one of the best fans of this blog :)

    Best regards.

    H.Tonguç Yılmaz

    February 20, 2009 at 11:39 am

  2. Tonguc.

    Thank your for interest and compliment.

    Dion Cho

    February 20, 2009 at 12:39 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 58 other followers

%d bloggers like this: