WITH SUBQUERY

基本情報

 

Parameter情報

 

SWAP_JOIN_INPUTS

 

/*+ SWAP_JOIN_INPUTS(table_name) */

 

1. Syntax : _WITH_SUBQUERY
2. 設定方法
  ・Parameter File
  ・Alter Session Set
  ・Alter System Set  
3. バージョンとデフォルト 
   11gR1 : OPTIMIZER

 

説明

 

11gからサポートされているパラメータであり、With Subqueryを処理する方法を決定します。次の3つの値を提供します。

 

・OPTIMIZER:Optimizerが実行を判断するデフォルトの動作方式でです。
・MATERIALIZE:常にMaterializeを実行します。/*+ materialize*/ヒントを与えたのと同じように動作します。
・INLINE:常にInlineモードで動作します。/*+ inline*/ヒントを与えたのと同じように動作します。

 

使用例は以下の通りである。

 

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 |
 ------------------------------------------------------------------

 

外部参照

 

1. _with_subquery parameter – controlling subquery factoring