꿈꾸는 개발자, DBA 커뮤니티 구루비

Complex View Merging

Complex View 는 Distinct 나 Group By 절이 포함된 View 를 의미한다.

Oracle 8i 까지는 Complex View 는 기본적으로 Merging 을 수행하지 않았다.
하지만 Oracle 9i 부터는 Complex View 또한 Merging 을 수행한다.
Complex View Merging 이 주는 효과는 Simple View Merging 과 동일하다.
Query Block 수를 줄임으로써 Optimization 이 보다 원활하게 이루어지도록 도와주는 것이다.

Complex View 를 사용하는 Query 에 대해 NO_MERGE Hint 를 사용해 View Merging 을 강제로 비활성화한 경우에는 다음과 같은 실행 계획을 보인다.


Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

SELECT /*+ gather_plan_statistics */
  t1.c1, v.c2
FROM
  t1,
  (SELECT /*+ no_merge */
        c1, COUNT(*) as c2
    FROM t2
    GROUP BY c1) v
WHERE
  t1.c1 = v.c1
; 
       997          1
       998          1
       999          1
      1000          1
1000 개의 행이 선택되었습니다.

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'allstats cost last'));
SQL_ID  5ug56utp0uncz, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */   t1.c1, v.c2 FROM   t1,   (SELECT
/*+ no_merge */         c1, COUNT(*) as c2     FROM t2     GROUP BY c1)
v WHERE   t1.c1 = v.c1

Plan hash value: 1333811612
---------------------------------------------------------------------------------------------------------------------------------
| 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 |      99 |       |       |          |
|*  1 |  HASH JOIN            |       |      1 |   1000 |    12  (17)|   1000 |00:00:00.03 |      99 |   968K|   968K| 1237K (0)|
|   2 |   VIEW                |       |      1 |   1000 |     4  (25)|   1000 |00:00:00.01 |       7 |       |       |          |
|   3 |    HASH GROUP BY      |       |      1 |   1000 |     4  (25)|   1000 |00:00:00.01 |       7 |   879K|   879K| 1263K (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 |      92 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

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

View Merging 이 이루어지지 않기 때문에, Oracle 은 Inline View 에 해당하는 Query Block을 Table Full Scan 으로 Optimization 하고 그 결과를 다시 Table t1 에 대한 Index Fast Full Scan 과 Hash Join 하는 것으로 Optimization 한다.

View Merging 이 성공적으로 이루어지는 경우에는 전혀 다른 실행 계획을 보인다.
아래에 그 결과가 있다.



SELECT /*+ gather_plan_statistics */
  t1.c1, v.c2
FROM
  t1,
  (SELECT /*+ merge */
        c1, COUNT(*) AS c2
    FROM t2
    GROUP BY c1) v
WHERE
  t1.c1 = v.c1
;

       981          1
       990          1
       993          1
       997          1
      1000          1
1000 개의 행이 선택되었습니다.
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'allstats cost last'));
SQL_ID  10db4x15xct6x, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */   t1.c1, v.c2 FROM   t1,   (SELECT
/*+ merge */         c1, COUNT(*) as c2     FROM t2     GROUP BY c1) v
WHERE   t1.c1 = v.c1

Plan hash value: 4227326106

----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |       |      1 |        |    11 (100)|   1000 |00:00:00.03 |      33 |       |       |          |
|   1 |  HASH GROUP BY         |       |      1 |   1000 |    11  (19)|   1000 |00:00:00.03 |      33 |   792K|   792K| 1266K (0)|
|*  2 |   HASH JOIN            |       |      1 |   1000 |    10  (10)|   1000 |00:00:00.02 |      33 |  1066K|  1066K| 1194K (0)|
|   3 |    INDEX FAST FULL SCAN| T2_N1 |      1 |   1000 |     2   (0)|   1000 |00:00:00.01 |       8 |       |       |          |
|   4 |    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"="C1")

위의 실행계획을 이용해 역으로 추론해보면 Complex View Merging 에 의해 Query 가 다음과 같이 Transformation 되었다는 것을 알 수 있다.



SELECT 
	t2.c1, COUNT(*) AS c2
FROM
	t1, t2
WHERE 
	t1.c1 = t2.c1
GROUP BY 
	t2.c1
;
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'allstats cost last'));
SQL_ID  2rbvq1tgav575, child number 0
-------------------------------------
SELECT  t2.c1, COUNT(*) AS c2 FROM  t1, t2 WHERE  t1.c1 = t2.c1 GROUP
BY  t2.c1

Plan hash value: 4227326106

----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |       |      1 |        |    11 (100)|   1000 |00:00:00.02 |      33 |       |       |          |
|   1 |  HASH GROUP BY         |       |      1 |   1000 |    11  (19)|   1000 |00:00:00.02 |      33 |   879K|   879K| 1246K (0)|
|*  2 |   HASH JOIN            |       |      1 |   1000 |    10  (10)|   1000 |00:00:00.02 |      33 |  1066K|  1066K| 1212K (0)|
|   3 |    INDEX FAST FULL SCAN| T2_N1 |      1 |   1000 |     2   (0)|   1000 |00:00:00.01 |       8 |       |       |          |
|   4 |    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"="T2"."C1")

View Merging 에 의해 Inline View 가 없어지면서 Query 가 단순해지고 이로 인해 보다 합리적인 실행 계획을 수립할 수 있게 된다.