REMOVE AGGR SUBQUERY

EXEM Knowledge Base

Jump to: navigation, 찾기

목차

[편집] 기본 정보

[편집] Parameter 정보


Syntax _REMOVE_AGGR_SUBQUERY = TRUE|FALSE
설정방법
  • Parameter File
  • ALTER SYSTEM SET "_REMOVE_AGGR_SUBQUERY" =
  • ALTER SESSION SET "_REMOVE_AGGR_SUBQUERY" =
버전 및 기본값
9iR2 10gR1 10gR2 11g
- TRUE TRUE 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 |
----------------------------------------------------------------------------------------------



[편집] 외부 참조