Non Semi AND Anti Join

모든 Subquery 가 Semi Join 으로 Unnesting 되는 것은 아니다. 아래 Query 를 보자.

{section}
{column:width=50}



C:\Users\sunshiny>sqlplus scott/tiger
SQL*Plus: Release 11.2.0.1.0 Production on 수 4월 13 22:25:32 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
다음에 접속됨:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> CREATE TABLE t1(c1 INT, c2 CHAR(10), c3 INT);

테이블이 생성되었습니다.

SQL> CREATE TABLE t2(c1 INT, c2 CHAR(10), c3 INT);

테이블이 생성되었습니다.

SQL> CREATE TABLE t3(c1 INT, c2 CHAR(10), c3 INT);

테이블이 생성되었습니다.

SQL> CREATE INDEX t1_n1 ON t1(c1);

인덱스가 생성되었습니다.

SQL> CREATE INDEX t2_n1 ON t2(c1);

인덱스가 생성되었습니다.

SQL> CREATE INDEX t3_n1 ON t3(c1);

인덱스가 생성되었습니다.

SQL> ALTER SESSION SET "_optimizer_cost_based_transformation" = off;

세션이 변경되었습니다.

SQL> ALTER SESSION SET "_optimizer_push_pred_cost_based" = false;

세션이 변경되었습니다.

SQL> INSERT INTO t1
  2  SELECT level              c1    -- 유일값(1~10000)
  3       , 'dummy'            c2    -- 동일값('dummy')
  4       , MOD(level, 10) + 1 c3    -- 10개값(1~10)
  5    FROM dual
  6   CONNECT BY level <= 10000
  7  ;

10000 개의 행이 만들어졌습니다.

SQL> INSERT INTO t2
  2  SELECT level
  3       , 'dummy'
  4       , MOD(level, 10) + 1
  5    FROM dual
  6   CONNECT BY level <= 1000
  7  ;

1000 개의 행이 만들어졌습니다.

SQL> INSERT INTO t3
  2  SELECT level
  3       , 'dummy'
  4       , MOD(level, 10) + 1
  5    FROM dual
  6   CONNECT BY level <= 100
  7  ;

100 개의 행이 만들어졌습니다.

SQL> COMMIT;

커밋이 완료되었습니다.

SQL> EXEC dbms_stats.gather_table_stats(user, 'T1');

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> EXEC dbms_stats.gather_table_stats(user, 'T2');

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> EXEC dbms_stats.gather_table_stats(user, 'T3');

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> SELECT COUNT(*)
  2     FROM (SELECT /*+ gather_plan_statistics */
  3               t1.c1, t1.c2
  4               FROM t1
  5               WHERE
  6               t1.c1 IN (SELECT ROWNUM FROM t2)
  7          )
  8  ;

{column}
{column:width=50}
{column}
{section}

ROWNUM 이 포함된 Subquery 는 Main Query 안으로 바로 Unnesting 하는 것이 불가능하다.
그렇다고 해서 무조건 Filter Operation 을 사용한다면 매우 불행한 선택이 될 것이다.
이 경우 Oracle 은 Subquery 를 View 로 변환 하고 가능한 Filter Operation 을 회피하게끔 실행 계획을 수립한다.

{section}
{column:width=50}



SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'allstats cost last'));
SQL_ID  f0cpgyqcch33j, child number 0
-------------------------------------
SELECT COUNT(*)  FROM (SELECT /*+ gather_plan_statistics */      t1.c1,
t1.c2    FROM t1    WHERE      t1.c1 IN (SELECT ROWNUM FROM t2)    )

Plan hash value: 2572059326

-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |      1 |        |    12 (100)|      1 |00:00:00.03 |      32 |       |       |  |
|   1 |  SORT AGGREGATE        |          |      1 |      1 |            |      1 |00:00:00.03 |      32 |       |       |  |
|*  2 |   HASH JOIN            |          |      1 |   1000 |    12  (17)|   1000 |00:00:00.03 |      32 |  1066K|  1066K| 1205K (0)|
|   3 |    VIEW                | VW_NSO_1 |      1 |   1000 |     4  (25)|   1000 |00:00:00.01 |       7 |       |       |  |
|   4 |     HASH UNIQUE        |          |      1 |   1000 |     4  (25)|   1000 |00:00:00.01 |       7 |  1037K|  1037K| 1262K (0)|
|   5 |      COUNT             |          |      1 |        |            |   1000 |00:00:00.01 |       7 |       |       |  |
|   6 |       TABLE ACCESS FULL| T2       |      1 |   1000 |     3   (0)|   1000 |00:00:00.01 |       7 |       |       |  |
|   7 |    INDEX FAST FULL SCAN| T1_N1    |      1 |  10000 |     7   (0)|  10000 |00:00:00.01 |      25 |       |       |  |
-------------------------------------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T1"."C1"="ROWNUM")

{column}
{column:width=50}
{column}
{section}

위의 실행 계획을 보면 Subquery 를 View 로 변환하고, 이 View를 Table T1과 Hash Join 하게끔 선택된 것을 알 수 있다.
덕분에 32 Black의 Logical Reads만으로 원하는 결과를 얻을 수 있다.
이것이 최적의 실행 계획인가? Subquery Unnesting이 이루어지지 않은 경우와 비교하면 Oracle의 선택이 옳았음을 알 수 있다.

{section}
{column:width=50}


SQL> SELECT COUNT(*)
  2     FROM (SELECT /*+ gather_plan_statistics */
  3             t1.c1, t1.c2
  4             FROM
  5             t1
  6             WHERE
  7             t1.c1 IN (SELECT /*+ no_unnest */ ROWNUM FROM t2)
  8             )
  9  ;


SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'allstats cost last'));
SQL_ID  a4f96mbz2gbv0, child number 0
-------------------------------------
SELECT COUNT(*)  FROM (SELECT /*+ gather_plan_statistics */      t1.c1, t1.c2    
                          FROM      t1    
                          WHERE      t1.c1 IN (SELECT /*+ no_unnest */
                                                 ROWNUM FROM t2)    )

Plan hash value: 2881437346

-----------------------------------------------------------------------------------------------------
| Id  | Operation             | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |      1 |        | 10014 (100)|      1 |00:00:13.47 |   68883 |
|   1 |  SORT AGGREGATE       |      |      1 |      1 |            |      1 |00:00:13.47 |   68883 |
|*  2 |   FILTER              |      |      1 |        |            |   1000 |00:00:13.47 |   68883 |
|   3 |    TABLE ACCESS FULL  | T1   |      1 |  10000 |    11   (0)|  10000 |00:00:00.01 |      38 |
|*  4 |    FILTER             |      |  10000 |        |            |   1000 |00:00:13.44 |   68845 |
|   5 |     COUNT             |      |  10000 |        |            |   9500K|00:00:10.06 |   68845 |
|   6 |      TABLE ACCESS FULL| T2   |  10000 |      1 |     2   (0)|   9500K|00:00:05.99 |   68845 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter( IS NOT NULL)
   4 - filter(ROWNUM=:B1)

{column}
{column:width=50}
{column}
{section}

동일한 결과를 얻기 위해 무려 68,883(도서:67,713) Block의 Logical Reads가 발생한다.
Subquery Unnesting이 얼마나 중요한지 다시 한번 확인할 수 있다.

UNION ALL 과 같은 Operation 에서도 동일한 현상이 발생한다.

{section}
{column:width=50}


SELECT /*+ gather_plan_statistics */
	  t1.c1, t1.c2
	FROM
	  t1
	WHERE
	  t1.c1 IN (SELECT c1 FROM t2 
			UNION ALL
		    SELECT c1 FROM t3)
;

{column}
{column:width=50}
{column}
{section}

UNION ALL 이 사용된 경우에도 Subquery 가 View 로 전환되고, View 에 대해 Hash Join 이 수행되는 방식의 Unnesting 이 이루어 진다.

{section}
{column:width=50}


SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'allstats cost last'));
SQL_ID  7nx65u3p79p0g, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */    t1.c1, t1.c2  FROM    t1  WHERE t1.c1 IN (SELECT c1 FROM t2     
                                                                                    UNION ALL     
                                                                                  SELECT c1 FROM t3)
Plan hash value:3011680408

------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |      1 |        |    18 (100)|   1000 |00:00:00.03 |     119 |       |       | |
|*  1 |  HASH JOIN            |          |      1 |   1100 |    18   (6)|   1000 |00:00:00.03 |     119 |  1066K|  1066K| 1194K (0)|
|   2 |   VIEW                | VW_NSO_1 |      1 |   1100 |     6   (0)|   1000 |00:00:00.01 |      14 |       |       | |
|   3 |    HASH UNIQUE        |          |      1 |   1100 |     6  (50)|   1000 |00:00:00.01 |      14 |  1037K|  1037K| 1260K (0)|
|   4 |     UNION-ALL         |          |      1 |        |            |   1100 |00:00:00.01 |      14 |       |       | |
|   5 |      TABLE ACCESS FULL| T2       |      1 |   1000 |     3   (0)|   1000 |00:00:00.01 |       7 |       |       | |
|   6 |      TABLE ACCESS FULL| T3       |      1 |    100 |     3   (0)|    100 |00:00:00.01 |       7 |       |       | |
|   7 |   TABLE ACCESS FULL   | T1       |      1 |  10000 |    11   (0)|  10000 |00:00:00.01 |     105 |       |       | |
------------------------------------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."C1"="C1")

{column}
{column:width=50}
{column}
{section}

즉각적인 Unnesting 이 발생하지 않은 Subquery 를 View 로 전환하는 것은 매우 효과적인 접근 방식이다.
View 에 대해 적용 가능한 다른 최적화 기법을 추가적으로 사용할 수 있기 때문이다. 아래 예제를 보자.

{section}
{column:width=50}


SELECT /*+ gather_plan_statistics */
	  t1.c1, t1.c2
	FROM
	  t1
	WHERE
	  t1.c1 IN (SELECT c1 FROM t2 
		      UNION ALL
		    SELECT c1 FROM t3)
	  AND t1.c1 BETWEEN 1 AND 100
;

{column}
{column:width=50}
{column}
{section}

UNION ALL 과 함께 Subquery 의 바깥에서 t1.c1 between 1 and 100 조건이 부여되었다.
이 조건(Predicate)은 View 안으로 Push 될 수 있다.
Predicate Pushing 이라는 이름의 Transformation 기법이다.
덕분에 다음과 같은 효율적인 실행 계획이 수립된다.

{section}
{column:width=50}


SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'allstats cost last'));
SQL_ID  5bxtkwx5p35s4, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */    t1.c1, t1.c2  FROM    t1  WHERE t1.c1 IN (SELECT c1 FROM t2     
                                                                                     UNION ALL     
                                                                                  SELECT c1 FROM t3)
AND t1.c1 BETWEEN 1 AND 100

Plan hash value: 4050798339

-------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |      1 |        |     7 (100)|    100 |00:00:00.01 |       6 |       |       |        |
|*  1 |  HASH JOIN                   |          |      1 |      2 |     7  (15)|    100 |00:00:00.01 |       6 |   870K|   870K| 1195K (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID| T1       |      1 |    100 |     3   (0)|    100 |00:00:00.01 |       3 |       |       |        |
|*  3 |    INDEX RANGE SCAN          | T1_N1    |      1 |    100 |     2   (0)|    100 |00:00:00.01 |       2 |       |       |        |
|   4 |   VIEW                       | VW_NSO_1 |      1 |    200 |     3   (0)|    100 |00:00:00.01 |       3 |       |       |        |
|   5 |    HASH UNIQUE               |          |      1 |    200 |     3  (34)|    100 |00:00:00.01 |       3 |  1067K|  1067K| 1259K (0)|
|   6 |     UNION-ALL                |          |      1 |        |            |    200 |00:00:00.01 |       3 |       |       |        |
|*  7 |      INDEX RANGE SCAN        | T2_N1    |      1 |    100 |     2   (0)|    100 |00:00:00.01 |       2 |       |       |        |
|*  8 |      INDEX RANGE SCAN        | T3_N1    |      1 |    100 |     1   (0)|    100 |00:00:00.01 |       1 |       |       |        |
-------------------------------------------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."C1"="C1")
   3 - access("T1"."C1">=1 AND "T1"."C1"<=100)
   7 - access("C1">=1 AND "C1"<=100)
   8 - access("C1">=1 AND "C1"<=100)

{column}
{column:width=50}
{column}
{section}

7번과 8번 단계에서 t1.c1 between 1 and 100 조건이 View 안으로 Pushing 되었다는 것을 알 수 있다.
그 덕분에 비효율적인 Table Full Scan 대신 보다 효율적인 Index Range Scan 이 선택되었다.
이런 극적인 변화는 Subquery Unnesting 의 긍정적인 효과 중 하나로 볼 수 있다.

Aggregate Subquery Elimination

Oracle 10g 는 Aggregate Subquery Elimination 이라는 이름의 새로운 Transformation 기법을 소개했다.
말 그대로 Aggregate Function 을 포함한 Subquery 를 (가능한) 없애겠다는 것이다.
즉 Main Query 안으로 넣어 버리겠다는 것이다.
이 과정에서 Subquery Unnesting 의 매우 특별한 형태가 구현된다.

Aggregate Subquery Elimination 은 _REMOVE_AGGR_SUBQUERY Parameter 로 제어하며 기본값은 True 이다.
즉, Oracle 은 가능한 Aggregate Subquery 를 없애려고 노력한다.
Aggregate Subquery Elimination 의 동작 여부에 따라 실행 계획이 어떻게 바뀌며 성능에는 어떤 영향을 주는지 알아보자.
우선 Aggregate Subquery Elimination 이 발생하지 않는 경우는 다음과 같다.

{section}
{column:width=50}


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

{column}
{column:width=50}
{column}
{section}

아래 결과를 보면 Table t2 를 두 번 Access 하는 것을 알 수 있다.
Subquery 에서 한번, Join 에서 한 번 Access 한다.

{section}
{column:width=50}



SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'allstats cost last'));
SQL_ID  b9v83r4pgzcs5, child number 0
-------------------------------------
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)

Plan hash value: 2760822941

------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name    | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |         |      1 |        |    15 (100)|   1000 |00:00:00.03 |     106 |       |       | |
|*  1 |  HASH JOIN             |         |      1 |   1000 |    15  (14)|   1000 |00:00:00.03 |     106 |   870K|   870K| 1223K (0)|
|   2 |   TABLE ACCESS FULL    | T2      |      1 |   1000 |     3   (0)|   1000 |00:00:00.01 |       7 |       |       | |
|*  3 |   HASH JOIN            |         |      1 |   1000 |    12  (17)|   1000 |00:00:00.02 |      99 |   870K|   870K| 1198K (0)|
|   4 |    VIEW                | VW_SQ_1 |      1 |   1000 |     4  (25)|   1000 |00:00:00.01 |       7 |       |       | |
|   5 |     HASH GROUP BY      |         |      1 |   1000 |     4  (25)|   1000 |00:00:00.01 |       7 |   848K|   848K| 1218K (0)|
|   6 |      TABLE ACCESS FULL | T2      |      1 |   1000 |     3   (0)|   1000 |00:00:00.01 |       7 |       |       | |
|   7 |    INDEX FAST FULL SCAN| T1_N1   |      1 |  10000 |     7   (0)|  10000 |00:00:00.01 |      92 |       |       | |
------------------------------------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."C1"="T2"."C1" AND "T2"."C2"="MAX(C2)")
   3 - access("ITEM_1"="T1"."C1")

{column}
{column:width=50}
{column}
{section}

반면 Aggregate Subquery Elimination 기능을 활성화(기본적으로 활성화되어 있음)하면 다음과 같이 실행 계획이 변한다.

{section}
{column:width=50}


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


SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'allstats cost last'));
SQL_ID  4akhckc977yd0, child number 0
-------------------------------------
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)

Plan hash value: 3825390570

--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |      1 |        |    12 (100)|   1000 |00:00:00.03 |      32 |       |       |   |
|*  1 |  VIEW                   | VW_WIF_1 |      1 |   1000 |    12  (17)|   1000 |00:00:00.03 |      32 |       |       |   |
|   2 |   WINDOW SORT           |          |      1 |   1000 |    12  (17)|   1000 |00:00:00.03 |      32 | 55296 | 55296 |49152(0)|
|*  3 |    HASH JOIN            |          |      1 |   1000 |    11  (10)|   1000 |00:00:00.02 |      32 |   804K|   804K| 1229K(0)|
|   4 |     TABLE ACCESS FULL   | T2       |      1 |   1000 |     3   (0)|   1000 |00:00:00.01 |    7 |          |          |
|   5 |     INDEX FAST FULL SCAN| T1_N1    |      1 |  10000 |     7   (0)|  10000 |00:00:00.01 |      25 |       |       |   |
--------------------------------------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("VW_COL_3" IS NOT NULL)
   3 - access("T1"."C1"="T2"."C1")

{column}
{column:width=50}
{column}
{section}

Table t2 에 대한 Access 가 한번으로 줄어들고 그만큼 Cost 와 Logical Reads 도 줄어든다.
긍정적인 성능 개선 효과가 있는 셈이다.
한 가지 이상한 변화는 WINDOWS SORT (2번 단계) Operation 이 추가적으로 관찰된다는 것이다.
WINDOWS SORT Operation 은 Analytic Function 을 사용한 경우에 주로 나타난다.
하지만 Query 어디에도 Analytic Function 이 사용된 흔적은 없다. 무슨 일이 발생한 것인가.

다행히 DBMS_XPLAN.DISPLAY_CURSOR Function 이 제공하는 기능을 이용하면 한 가지 Hint를 얻을 수 있다.
Function 호출 시 다음과 같이 ALL Option 을 지정하면 Column Projection 정보가 추가로 출력 된다.


   SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'ALL allstats cost last'));

Column Projection 이란 실행 계획의 각 단계에서 어떤 Column 값을 사용하는지를 의미한다.
Aggregate Subquery Elimination 이 발생한 경우의 Column Projection 정보는 다음과 같다.
WINDOWS SORT Operation 이 등장한 2번 단계를 주목하자.

{section}
{column:width=50}



SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'all allstats cost last'));
SQL_ID  4akhckc977yd0, child number 0
-------------------------------------
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)

Plan hash value: 3825390570

---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |      1 |        |       |    12 (100)|          |   1000 |00:00:00.03 |      32 |   |       |          |
|*  1 |  VIEW                   | VW_WIF_1 |      1 |   1000 | 37000 |    12  (17)| 00:00:01 |   1000 |00:00:00.03 |      32 |   |       |          |
|   2 |   WINDOW SORT           |          |      1 |   1000 | 22000 |    12  (17)| 00:00:01 |   1000 |00:00:00.03 |      32 | 55296 | 55296 |49152  (0)|
|*  3 |    HASH JOIN            |          |      1 |   1000 | 22000 |    11  (10)| 00:00:01 |   1000 |00:00:00.02 |      32 |   804K|   804K| 1215K (0)|
|   4 |     TABLE ACCESS FULL   | T2       |      1 |   1000 | 18000 |     3   (0)| 00:00:01 |   1000 |00:00:00.01 |       7 |   |       |          |
|   5 |     INDEX FAST FULL SCAN| T1_N1    |      1 |  10000 | 40000 |     7   (0)| 00:00:01 |  10000 |00:00:00.01 |      25 |   |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------------------


Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$4F4DF0AE / VW_WIF_1@SEL$F79C84EE
   2 - SEL$4F4DF0AE
   4 - SEL$4F4DF0AE / T2@SEL$1
   5 - SEL$4F4DF0AE / T1@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("VW_COL_3" IS NOT NULL)
   3 - access("T1"."C1"="T2"."C1")

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "ITEM_1"[NUMBER,22], "ITEM_2"[CHARACTER,10], "VW_COL_3"[ROWID,10]
   2 - (#keys=1) "T2"."C1"[NUMBER,22], "T2"."C2"[CHARACTER,10], "T1"."C1"[NUMBER,22], ROWID[ROWID,10], 
                       MAX("C2") OVER ( PARTITION BY "S"."C1")[10]
   3 - (#keys=1) "T2"."C1"[NUMBER,22], "T1"."C1"[NUMBER,22], ROWID[ROWID,10], "T2"."C2"[CHARACTER,10]
   4 - ROWID[ROWID,10], "T2"."C1"[NUMBER,22], "T2"."C2"[CHARACTER,10]
   5 - "T1"."C1"[NUMBER,22]

{column}
{column:width=50}
{column}
{section}

2 번 단계에서 원래 Query 에는 없던 MAX(C2) OVER (PARTITION BY S.C1) Operation 이 추가된 것을 확인할 수 있다.
Query Transformation 이 발생한 것이다.
Subquery 에 있던 MAX(C2) Column 이 Subquery Unnesting 과 함께 Main Query 안으로 들어간 것으로 해석할 수 있다.

Oracle 의 Optimizer 가 점점 개선됨에 따라 새로운 유형의 Transformation 기법이 계속해서 추가된다.
이런 변화가 때로는 극적이어서 전혀 예상치 못한 실행 계획 상의 변화가 생기기도 한다.
Aggregate Subquery Elimitnation 이 이런 기법 중 하나이다.
단순히 Subquery 를 Unnesting 하는데 그치지 않고 Analytic Fuction 을 사용하는 새로운 Column 을 생성함으로써 매우 극적인 변화를 일으킨다.
이런 변화를 감지하고 대응하는 유일한 방법은 DBMS_XPLAN Package 를 이용해서 실행 계획 상의 모든 변화를 잘 관찰하는 것이다.
단순히 Row Source Operation 의 변화에 그치지 않고 Predicate Information 과 Column Projection Information, 그리고 Outline 의 변화 까지 관찰해야만 완벽한 해석이 가능하다.

문서에 대하여

최초작성일 : 2011년 04월 13일
이 문서는 오라클클럽 대용량 데이터베이스 스터디 모임에서 작성하였습니다.
{*}이 문서의 내용은 (주)엑셈에서 출간한 'Optimizing Oracle Optimizer'를 참고하였습니다.*