2018.01.17
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