WITH SUBQUERY - 日本エクセム株式会社 Oracleパラメータ
基本情報
パラメータ情報
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
