REMOVE AGGR SUBQUERY

Syntax   : _REMOVE_AGGR_SUBQUERY = TRUE|FALSE
設定方法  : ・Parameter File
           ・ALTER SYSTEM SET “_REMOVE_AGGR_SUBQUERY” =
　　　　　　・ALTER SESSION SET “_REMOVE_AGGR_SUBQUERY” =

バージョンとデフォルト
9iR2  : ー
10gR1 : TRUE	
10gR2 : TRUE	
11g   : TRUE	

Aggregate Functionを含むSubqueryをUnnestingてMain QueryのAnalytic Functionに変換する機能を提供します。このような機能をAggregate Subquery Eliminationと呼びます。

Aggregate Subquery Eliminationが動作しない場合は、次のように実行されます。テーブルT2のAggregate SubqueryをViewに変換した後、テーブルT1とHash Joinし、その結果を再度テーブルT2とHash Joinします。この過程で、テーブルT2を二度アクセスするオーバーヘッドが発生します。

select /*+ gather_plan_statistics 
          opt_param('_remove_aggr_subquery','false') */
  t1.c1, t2.c2
from 
  t1, t2
where
  t1.c1 = t2.c1 and 
  t2.c2 = (select max(c2) from t2 s where s.c1 = t1.c1)
;

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

--------------------------------------------------------------------------------------------
| Id  | Operation              | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN             |         |      1 |   1000 |   1000 |00:00:00.18 |     105 |
|   2 |   TABLE ACCESS FULL    | T2      |      1 |   1000 |   1000 |00:00:00.01 |       7 |
|*  3 |   HASH JOIN            |         |      1 |   1000 |   1000 |00:00:00.15 |      98 |
|   4 |    VIEW                | VW_SQ_1 |      1 |   1000 |   1000 |00:00:00.02 |       7 |
|   5 |     HASH GROUP BY      |         |      1 |   1000 |   1000 |00:00:00.01 |       7 |
|   6 |      TABLE ACCESS FULL | T2      |      1 |   1000 |   1000 |00:00:00.01 |       7 |
|   7 |    INDEX FAST FULL SCAN| T1_N1   |      1 |  10000 |  10000 |00:00:00.04 |      91 |
--------------------------------------------------------------------------------------------

Aggregate Subquery Eliminationが動作している場合には、次のような実行パスを示します。Subqueryがなくなり、Analytic Function（WINDOWS SORT）に変換されたことを確認することができます。これにより、テーブルT2の重複的なアクセスが消え仕事量（Buffers。Logical Reads）も改善されたことを確認することができます。

select /*+ gather_plan_statistics 
          opt_param('_remove_aggr_subquery','true') */
   t1.c1, t2.c2
from 
  t1, t2
where
  t1.c1 = t2.c1 and 
  t2.c2 = (select max(c2) from t2 s where s.c1 = t1.c1)
;

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

----------------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------
|*  1 |  VIEW                   | VW_WIF_1 |      1 |   1000 |   1000 |00:00:00.15 |      31 |
|   2 |   WINDOW SORT           |          |      1 |   1000 |   1000 |00:00:00.14 |      31 |
|*  3 |    HASH JOIN            |          |      1 |   1000 |   1000 |00:00:00.14 |      31 |
|   4 |     TABLE ACCESS FULL   | T2       |      1 |   1000 |   1000 |00:00:00.01 |       7 |
|   5 |     INDEX FAST FULL SCAN| T1_N1    |      1 |  10000 |  10000 |00:00:00.03 |      24 |
----------------------------------------------------------------------------------------------