| Non-Mergeable views |

Oracle 은 가능한 View Merging 을 시도한다.
하지만 항상 성공하는 것은 아니다.
Oracle Manual 에서는 다음과 같은 View 들을 Non-Mergeable Views, 즉 View Merging 이 불가능한 View 로 정의하고 있다.

Set operators (UNION, UNION ALL, INTERSECT, MINUS)
A CONNECT BY clause
A ROWNUM pseudo column
Aggregate functions (AVG, COUNT, MAX, MIN, SUM) in the select list

하지만 실제로 확인을 해보면 네 번째 경우, 즉 Aggregate Function 이 사용된 경우에는 Merging 에 성공할 때도 있고 실패할 때도 있다.
그 외에도 Cursor Expression 이 Main Query 에 사용된 경우에도 View Merging 이 이루어지지 않는다.
또한 Analytic Function 이 View 에 서 사용된 경우에도 View Merging 이 이루어 지지 않는다.

간단한 예제를 통해 View Merging 이 이루어지지 않는 경우들을 확인해 보자.
View 내에 Set Operation 이 있는 경우에는 View Merging 이 이루어지지 않는다.

{section}
{column:width=50}


-- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
-- Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production

SQL> SELECT /*+ gather_plan_statistics */
  2    t1.c1, v.c2
  3  FROM
  4    t1,
  5    (SELECT c1, c2, c3 FROM t2
  6      UNION ALL
  7     SELECT c1, c2, c3 FROM t3) v
  8  WHERE
  9    t1.c1 = v.c1 AND
 10    v.c3 = 1
 11  ;
         1 dummy
         1 dummy
       101 dummy
       201 dummy
       301 dummy
       401 dummy
       501 dummy
       601 dummy
       701 dummy
       801 dummy
       901 dummy
11 개의 행이 선택되었습니다.

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

아래 실행 계획을 보면 2번 단계에서 View Operation 이 사용된 것을 알 수 있다.
View Merging 이 이루어지지 않은 것이다.
하지만 생각보다는 실행 계획이 훨씬 양호하다는 것을 알게 될 것이다.
View Merging 이 이루어지지 않았다면 Table t2 와 Table t3 에 대한 Table Full Scan 이 발생했을 것이다.
하지만 실제로는 성공적으로 Table t3 에 대해서는 Index Range Scan 이 성공적으로 수행된다.
Oracle 은 View Merging 이 실패하는 경우에는 Predicate Pushing 을 시도한다.
그 덕분에 v.c3 = 1 조건이 Inline View 안으로 삽입되며, 아래 결과에서는 Index Range Scan 으로 나타난다.
4번 단계와 5번 단계의 Predicate 를 유의해서 관찰하기 바란다.

{section}
{column:width=50}


-- Need Check --
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'allstats cost last'));
SQL_ID  akgr7bafd1kvp, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */   t1.c1, v.c2 FROM   t1,   (SELECT
c1, c2, c3 FROM t2     UNION ALL    SELECT c1, c2, c3 FROM t3) v WHERE
 t1.c1 = v.c1 AND   v.c3 = 1

-- Oracle 11g
-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |       |      1 |        |    12 (100)|     11 |00:00:00.02 |      35 |       |       |       |
|*  1 |  HASH JOIN                     |       |      1 |     11 |    12   (9)|     11 |00:00:00.02 |      35 |   870K|   870K| 1082K (0)|
|   2 |   VIEW                         |       |      1 |     11 |     4   (0)|     11 |00:00:00.01 |       9 |       |       |       |
|   3 |    UNION-ALL                   |       |      1 |        |            |     11 |00:00:00.01 |       9 |       |       |       |
|*  4 |     TABLE ACCESS FULL          | T2    |      1 |     10 |     3   (0)|     10 |00:00:00.01 |       7 |       |       |       |
|   5 |     TABLE ACCESS BY INDEX ROWID| T3    |      1 |      1 |     2   (0)|      1 |00:00:00.01 |       2 |       |       |       |
|*  6 |      INDEX RANGE SCAN          | T3_N2 |      1 |      1 |     1   (0)|      1 |00:00:00.01 |       1 |       |       |       |
|   7 |   INDEX FAST FULL SCAN         | T1_N1 |      1 |  10000 |     7   (0)|  10000 |00:00:00.01 |      26 |       |       |       |
-----------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."C1"="V"."C1")
   4 - filter("C3"=1)
   6 - access("C3"=1)

-- Oracle 10g
------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN                     |       |      1 |     11 |    10  (10)|     11 |00:00:00.01 |      33 |   870K|   870K| 1015K (0)|
|   2 |   VIEW                         |       |      1 |     11 |     3   (0)|     11 |00:00:00.01 |       8 |       |       |          |
|   3 |    UNION-ALL PARTITION         |       |      1 |        |            |     11 |00:00:00.01 |       8 |       |       |          |
|*  4 |     TABLE ACCESS FULL          | T2    |      1 |     10 |     3   (0)|     10 |00:00:00.01 |       6 |       |       |          |
|   5 |     TABLE ACCESS BY INDEX ROWID| T3    |      1 |      1 |     2   (0)|      1 |00:00:00.01 |       2 |       |       |          |
|*  6 |      INDEX RANGE SCAN          | T3_N2 |      1 |      1 |     1   (0)|      1 |00:00:00.01 |       1 |       |       |          |
|   7 |   INDEX FAST FULL SCAN         | T1_N1 |      1 |  10000 |     6   (0)|  10000 |00:00:00.01 |      25 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------

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

   1 - access("T1"."C1"="V"."C1")
   4 - filter("C3"=1)
   6 - access("C3"=1)

-- 1 교재에서는 NESTED LOOPS 사용
-- 7 교재에서는 INDEX RANGE SCAN 사용

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

아래 예와 같이 복잡한 Aggregate Function 이 사용된 Inline View 는 View Merging 이 이루어지지 않는다.

{section}
{column:width=50}



SQL> SELECT /*+ gather_plan_statistics */
  2    t1.c1, v.c2
  3  FROM
  4    t1,
  5    (SELECT c1, MAX(c2) AS c2, MAX(c3) AS c3
  6      FROM t2
  7      GROUP BY c1) v
  8  WHERE
  9    t1.c1 = v.c1 AND
 10    v.c3 = 1
 11  ;
         1 dummy
       101 dummy
       201 dummy
       301 dummy
       401 dummy
       501 dummy
       601 dummy
       701 dummy
       801 dummy
       901 dummy
10 개의 행이 선택되었습니다.
-- Need Check --
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'allstats cost last'));
SQL_ID  g7b17adzrfpjf, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */   t1.c1, v.c2 FROM   t1,   (SELECT
c1, MAX(c2) AS c2, MAX(c3) AS c3     FROM t2     GROUP BY c1) v WHERE
t1.c1 = v.c1 AND   v.c3 = 1

-- Oracle 11g
---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |      1 |        |    12 (100)|     10 |00:00:00.02 |      33 |       |       |          |
|*  1 |  HASH JOIN            |       |      1 |     10 |    12  (17)|     10 |00:00:00.02 |      33 |   870K|   870K| 1076K (0)|
|   2 |   VIEW                |       |      1 |     10 |     4  (25)|     10 |00:00:00.01 |       7 |       |       |          |
|*  3 |    FILTER             |       |      1 |        |            |     10 |00:00:00.01 |       7 |       |       |          |
|   4 |     HASH GROUP BY     |       |      1 |     10 |     4  (25)|   1000 |00:00:00.01 |       7 |   766K|   766K| 1253K (0)|
|   5 |      TABLE ACCESS FULL| T2    |      1 |   1000 |     3   (0)|   1000 |00:00:00.01 |       7 |       |       |          |
|   6 |   INDEX FAST FULL SCAN| T1_N1 |      1 |  10000 |     7   (0)|  10000 |00:00:00.01 |      26 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."C1"="V"."C1")
   3 - filter(MAX("C3")=1)

-- Oracle 10g
---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN            |       |      1 |     10 |    11  (19)|     10 |00:00:00.01 |      31 |   870K|   870K| 1023K (0)|
|   2 |   VIEW                |       |      1 |     10 |     4  (25)|     10 |00:00:00.01 |       6 |       |       |          |
|*  3 |    FILTER             |       |      1 |        |            |     10 |00:00:00.01 |       6 |       |       |          |
|   4 |     HASH GROUP BY     |       |      1 |     10 |     4  (25)|   1000 |00:00:00.01 |       6 |       |       |          |
|   5 |      TABLE ACCESS FULL| T2    |      1 |   1000 |     3   (0)|   1000 |00:00:00.01 |       6 |       |       |          |
|   6 |   INDEX FAST FULL SCAN| T1_N1 |      1 |  10000 |     6   (0)|  10000 |00:00:00.01 |      25 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------

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

   1 - access("T1"."C1"="V"."C1")
   3 - filter(MAX("C3")=1)

-- 1 교재에서는 NESTED LOOPS 사용
-- 6 교재에서는 INDEX RANGE SCAN 사용

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

위의 Query 에서 비록 View Merging 은 이루어지지 않았지만 v.c3 = 1 조건이 View 안으로 Pushing 되어(3번 단계의 Predicate 참조), 비효율성이 상당히 감소된 것을 알 수 있다.
View Merging 과 Predicate Pushing 을 항상 같이 관찰해야 실행 계획을 정확하게 해석할 수 있다.

ROWNUM Operation 이 사용된 View 또한 Merging 이 이루어지지 않는다.

{section}
{column:width=50}



SQL> SELECT /*+ gather_plan_statistics */
  2    t1.c1, v.c2
  3  FROM
  4    t1,
  5    (SELECT ROWNUM AS r, c1, c2, c3
  6      FROM t2) v
  7  WHERE
  8    t1.c1 = v.c1 AND
  9    v.c3 = 1
 10  ;
         1 dummy
       101 dummy
       201 dummy
       301 dummy
       401 dummy
       501 dummy
       601 dummy
       701 dummy
       801 dummy
       901 dummy
10 개의 행이 선택되었습니다.
-- Need Check --
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'allstats cost last'));
SQL_ID  8h6g72wb5rgyy, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */   t1.c1, v.c2 FROM   t1,   (SELECT
ROWNUM AS r, c1, c2, c3     FROM t2) v WHERE   t1.c1 = v.c1 AND   v.c3 = 1

-- Oracle 11g
---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |      1 |        |    11 (100)|     10 |00:00:00.02 |      33 |       |       |          |
|*  1 |  HASH JOIN            |       |      1 |   1000 |    11  (10)|     10 |00:00:00.02 |      33 |   870K|   870K| 1082K (0)|
|*  2 |   VIEW                |       |      1 |   1000 |     3   (0)|     10 |00:00:00.01 |       7 |       |       |          |
|   3 |    COUNT              |       |      1 |        |            |   1000 |00:00:00.01 |       7 |       |       |          |
|   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 |      26 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."C1"="V"."C1")
   2 - filter("V"."C3"=1)

-- Oracle 10g
---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN            |       |      1 |   1000 |    10  (10)|     10 |00:00:00.01 |      31 |   870K|   870K| 1017K (0)|
|*  2 |   VIEW                |       |      1 |   1000 |     3   (0)|     10 |00:00:00.01 |       6 |       |       |          |
|   3 |    COUNT              |       |      1 |        |            |   1000 |00:00:00.01 |       6 |       |       |          |
|   4 |     TABLE ACCESS FULL | T2    |      1 |   1000 |     3   (0)|   1000 |00:00:00.01 |       6 |       |       |          |
|   5 |   INDEX FAST FULL SCAN| T1_N1 |      1 |  10000 |     6   (0)|  10000 |00:00:00.01 |      25 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------

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

   1 - access("T1"."C1"="V"."C1")
   2 - filter("V"."C3"=1)

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

위의 실행 계획을 보면 Aggregate Function 에 의해 View Merging 이 실패한 경우와는 또다른 형태를 보인다는 것을 알 수 있다.
v.c3 = 1 조건이 View 안으로 Pushing 되지 못하고 View 의 바깥에서 동작한다.
ROWNUM 연산자에 의해 View Merging 뿐만 아니라 Predicate Pushing 또한 실패하기 때문이다.
ROWNUM 을 Subquery 나 View 안에서 함부로 사용해서는 안 되는 이유이다.
재미있는 것은 ROWNUM 의 이런 속성을 이용해 일부러 불필요한 ROWNUM 을 사용하기도 한다는 것이다.
ROWNUM 의 속성을 잘 이해할 때 사용할 수 있는 Trick 이라고 할 수 있다.

Cursor Expression 은 Multi Row 를 Return 하는 Subquery 를 Select List 에서 사용할 수 있도록 해주는 강력한 기능이다.
하지만 Fetch 회수를 늘린다는 성능상의 단점이 존재한다.
또 하나의 문제점은 아래 예제와 같이 View Merging 을 지원하지 않는다는 것이다.

{section}
{column:width=50}



SQL> SELECT /*+ gather_plan_statistics */
  2    t1.c1, v.c2,
  3    CURSOR(SELECT * FROM t3 WHERE t3.c1 = t1.c1) AS cs
  4  FROM
  5    t1,
  6    (SELECT c1, c2, c3
  7      FROM t2) v
  8  WHERE
  9    t1.c1 = v.c1 AND
 10    v.c3 = 1
 11  ;
         1 dummy                CURSOR STATEMENT : 3
CURSOR STATEMENT : 3
         1 dummy                         1
       101 dummy                CURSOR STATEMENT : 3
CURSOR STATEMENT : 3
선택된 레코드가 없습니다.

       201 dummy                CURSOR STATEMENT : 3
CURSOR STATEMENT : 3
선택된 레코드가 없습니다.

       301 dummy                CURSOR STATEMENT : 3
CURSOR STATEMENT : 3
선택된 레코드가 없습니다.

       401 dummy                CURSOR STATEMENT : 3
CURSOR STATEMENT : 3
선택된 레코드가 없습니다.

       501 dummy                CURSOR STATEMENT : 3
CURSOR STATEMENT : 3
선택된 레코드가 없습니다.

       601 dummy                CURSOR STATEMENT : 3
CURSOR STATEMENT : 3
선택된 레코드가 없습니다.

       701 dummy                CURSOR STATEMENT : 3
CURSOR STATEMENT : 3
선택된 레코드가 없습니다.

       801 dummy                CURSOR STATEMENT : 3
CURSOR STATEMENT : 3
선택된 레코드가 없습니다.

       901 dummy                CURSOR STATEMENT : 3
CURSOR STATEMENT : 3
선택된 레코드가 없습니다.

10 개의 행이 선택되었습니다.
-- Need Check --
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'allstats cost last'));
SQL_ID  76mxg72avxqyw, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */   t1.c1, v.c2,   CURSOR(SELECT *
FROM t3 WHERE t3.c1 = t1.c1) AS csr FROM   t1,   (SELECT c1, c2, c3
FROM t2) v WHERE   t1.c1 = v.c1 AND   v.c3 = 1

-- Oracle 11g
---------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |      1 |        |    11 (100)|     10 |00:00:00.02 |   37 |          |       |    |
|   1 |  TABLE ACCESS BY INDEX ROWID| T3    |      0 |      1 |     2   (0)|      0 |00:00:00.01 |    0 |          |       |    |
|*  2 |   INDEX RANGE SCAN          | T3_N1 |      0 |      1 |     1   (0)|      0 |00:00:00.01 |    0 |          |       |    |
|*  3 |  HASH JOIN                  |       |      1 |     10 |    11  (10)|     10 |00:00:00.02 |   37 |   870K|   870K| 1055K (0)|
|   4 |   VIEW                      |       |      1 |     10 |     3   (0)|     10 |00:00:00.01 |    7 |          |       |    |
|*  5 |    TABLE ACCESS FULL        | T2    |      1 |     10 |     3   (0)|     10 |00:00:00.01 |    7 |          |       |    |
|   6 |   INDEX FAST FULL SCAN      | T1_N1 |      1 |  10000 |     7   (0)|  10000 |00:00:00.01 |   30 |          |       |    |
---------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T3"."C1"=:B1)
   3 - access("T1"."C1"="V"."C1")
   5 - filter("C3"=1)

-- Oracle 10g
---------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID| T3    |      0 |      1 |     2   (0)|      0 |00:00:00.01 |    0 |          |       |          |
|*  2 |   INDEX RANGE SCAN          | T3_N1 |      0 |      1 |     1   (0)|      0 |00:00:00.01 |    0 |          |       |          |
|*  3 |  HASH JOIN                  |       |      1 |     10 |    10  (10)|     10 |00:00:00.01 |   35 |   870K|   870K| 1017K (0)|
|   4 |   VIEW                      |       |      1 |     10 |     3   (0)|     10 |00:00:00.01 |    6 |          |       |          |
|*  5 |    TABLE ACCESS FULL        | T2    |      1 |     10 |     3   (0)|     10 |00:00:00.01 |    6 |          |       |          |
|   6 |   INDEX FAST FULL SCAN      | T1_N1 |      1 |  10000 |     6   (0)|  10000 |00:00:00.01 |   29 |          |       |          |
---------------------------------------------------------------------------------------------------------------------------------------

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

   2 - access("T3"."C1"=:B1)
   3 - access("T1"."C1"="V"."C1") 
   5 - filter("C3"=1)

-- 3 교재에서는 NESTED LOOPS 사용
-- 6 교재에서는 INDEX RANGE SCAN 사용

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

위의 결과를 보면 Main Query 에서 Cursor Expression 을 사용한 경우에는 매우 간단한 형태의 View 라고 해도 Merging 이 이루어지지 않는다는 것을 알 수 있다.
하지만 다행스럽게도 Predicate Pushing 은 성공적으로 이루어진다.

Analytic Function 이 사용된 경우에도 View Merging 에 실패한다.

{section}
{column:width=50}



SQL> SELECT /*+ gather_plan_statistics */
  2    t1.c1, v.*
  3  FROM
  4    t1,
  5    (SELECT ROW_NUMBER() OVER (ORDER BY c1) AS rn,
  6            c1, c2, c3
  7    FROM t2) v
  8  WHERE
  9    t1.c1 = v.c1 AND
 10    v.c3 = 1
 11  ;
         1          1          1 dummy                         1
       101        101        101 dummy                         1
       201        201        201 dummy                         1
       301        301        301 dummy                         1
       401        401        401 dummy                         1
       501        501        501 dummy                         1
       601        601        601 dummy                         1
       701        701        701 dummy                         1
       801        801        801 dummy                         1
       901        901        901 dummy                         1

10 개의 행이 선택되었습니다.

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

아래 결과를 보면 View Merging 에 실패했음(2번 단계)을 확인할 수 있다.
더 안타까운 것은 Predicate Pushing 에도 실패한다는 것이다.
v.c3 = 1 조건이 View 안으로 Pushing 되지 못하고 2번 단계에서 적용된다.
즉, Table t2 에 대해 Analytic Function 이 다 적용된 후에 비로소 c3 = 1 조건이 적용된다는 것이다.

{section}
{column:width=50}



SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'allstats cost last'));
SQL_ID  2tmvfh1zhn3p8, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */   t1.c1, v.* FROM   t1,   (SELECT
ROW_NUMBER() OVER (ORDER BY c1) AS rn,           c1, c2, c3   FROM t2)
v WHERE   t1.c1 = v.c1 AND   v.c3 = 1

Plan hash value: 784523758

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |      1 |        |    12 (100)|     10 |00:00:00.02 |      33 |       |       |          |
|*  1 |  HASH JOIN            |       |      1 |   1000 |    12  (17)|     10 |00:00:00.02 |      33 |   816K|   816K| 1073K (0)|
|*  2 |   VIEW                |       |      1 |   1000 |     4  (25)|     10 |00:00:00.01 |       7 |       |       |          |
|   3 |    WINDOW SORT        |       |      1 |   1000 |     4  (25)|   1000 |00:00:00.01 |       7 | 38912 | 38912 |34816  (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 |      26 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."C1"="V"."C1")
   2 - filter("V"."C3"=1)

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

Oracle Manual 은 Aggregate Function 이 사용된 경우에는 View Merging 이 실패한다고 기술하고 있다.
하지만 Aggregate Function 이 사용딘 View 가 항상 Merging 에 실패하는 것은 아니다.
아래와 같은 Query 는 View Merging 이 수행되지 않는다.

{section}
{column:width=50}



SQL> SELECT /*+ gather_plan_statistics */
  2    t1.c1, v.c2
  3  FROM
  4    t1,
  5    (SELECT c1, c3, COUNT(*) AS c2
  6      FROM t2
  7      GROUP BY c1, c3) v
  8  WHERE
  9    t1.c1 = v.c1 AND
 10    v.c3 = 1
 11  ;
         1          1
       101          1
       201          1
       301          1
       401          1
       501          1
       601          1
       701          1
       801          1
       901          1
10 개의 행이 선택되었습니다.
-- Need Check --
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'allstats cost last'));
SQL_ID  1j3wzw9a0nz11, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */   t1.c1, v.c2 FROM   t1,   (SELECT
c1, c3, COUNT(*) AS c2     FROM t2     GROUP BY c1, c3) v WHERE   t1.c1
= v.c1 AND   v.c3 = 1

-- Oracle 11g
---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |      1 |        |    12 (100)|     10 |00:00:00.02 |      33 |       |       |          |
|*  1 |  HASH JOIN            |       |      1 |      8 |    12  (17)|     10 |00:00:00.02 |      33 |   968K|   968K| 1082K (0)|
|   2 |   VIEW                |       |      1 |      8 |     4  (25)|     10 |00:00:00.01 |       7 |       |       |          |
|   3 |    HASH GROUP BY      |       |      1 |      8 |     4  (25)|     10 |00:00:00.01 |       7 |   848K|   848K| 1011K (0)|
|*  4 |     TABLE ACCESS FULL | T2    |      1 |     10 |     3   (0)|     10 |00:00:00.01 |       7 |       |       |          |
|   5 |   INDEX FAST FULL SCAN| T1_N1 |      1 |  10000 |     7   (0)|  10000 |00:00:00.01 |      26 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."C1"="V"."C1")
   4 - filter("C3"=1)

-- Oracle 10g
---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN            |       |      1 |     10 |    11  (19)|     10 |00:00:00.01 |      31 |   968K|   968K| 1016K (0)|
|   2 |   VIEW                |       |      1 |     10 |     4  (25)|     10 |00:00:00.01 |       6 |       |       |          |
|   3 |    HASH GROUP BY      |       |      1 |     10 |     4  (25)|     10 |00:00:00.01 |       6 |       |       |          |
|*  4 |     TABLE ACCESS FULL | T2    |      1 |     10 |     3   (0)|     10 |00:00:00.01 |       6 |       |       |          |
|   5 |   INDEX FAST FULL SCAN| T1_N1 |      1 |  10000 |     6   (0)|  10000 |00:00:00.01 |      25 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------

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

   1 - access("T1"."C1"="V"."C1")
   4 - filter("C3"=1)

-- 1 교재에서는 NESTED LOOPS 사용
-- 5 교재에서는 INDEX RANGE SCAN 사용

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

하지만 거의 비슷한 형태의 아래와 같은 Query 는 View Merging 이 성공적으로 이루어진다.

{section}
{column:width=50}



SQL> SELECT /*+ gather_plan_statistics */
  2    t1.c1, v.c2
  3  FROM
  4    t1,
  5    (SELECT c1, c3, COUNT(*) AS c2
  6      FROM t2
  7      GROUP BY c1, c3) v
  8  WHERE
  9    t1.c1 = v.c1 AND
 10    t1.c3 = 1 -- v.c3 = 1
 11  ;
       401          1
       201          1
       301          1
         1          1
       801          1
       901          1
       701          1
       101          1
       501          1
       601          1
10 개의 행이 선택되었습니다.

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

-- Oracle 11g
------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |    16 (100)|     10 |00:00:00.01 |      45 |    |          |          |
|   1 |  HASH GROUP BY      |      |      1 |    100 |    16  (13)|     10 |00:00:00.01 |      45 |   781K|   781K| 1002K (0)|
|*  2 |   HASH JOIN         |      |      1 |    100 |    15   (7)|     10 |00:00:00.01 |      45 |   870K|   870K| 1207K (0)|
|*  3 |    TABLE ACCESS FULL| T1   |      1 |    100 |    11   (0)|    100 |00:00:00.01 |      38 |    |          |          |
|   4 |    TABLE ACCESS FULL| T2   |      1 |   1000 |     3   (0)|   1000 |00:00:00.01 |       7 |    |          |          |
------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T1"."C1"="C1")
   3 - filter("T1"."C3"=1)

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

두 Query 의 차이는 View Merging 이 이루어지지 않는 경우에는 v.c3 = 1 조건이, View Merging 이 이루어지는 경우에는 t1.c3 = 1 조건이 사용된다는 것이다.
왜 이런 차이가 발생하는가? 10053 Event 를 이용하면 유용한 정보를 얻을 수 있다.

아래 내용은 View Merging 이 실패한 Query 에 대해 10053 Event 에 의해 생성도니 Trace File 의 일부를 발췌한 것이다.
Heuristics 에 의해, 더 정확하게 표현하면 CBO 의 Source Code 에서 사용하는 일종의 규칠(Rule. Heuristics)에 의해 View Merging 이 비효율적일 것으로 판단했기 때문에 View Merging 을 수행하지 않겠다는 것이다.
{note:title=Be Careful}
*************************************
Predicate Move-Around (PM)
*************************************
CVM: Not merging SEL$2 (#0) into SEL$1 (#0) due to heuristics.
{note}
왜 Optimizer 가 이런 판단을 했는지는 정확하게 알 수 없다.
Optimizer 의 Transformation과 관련된 Code 에 존재하는 일부 사전적인 확인 절차에 의해 발생했을 것으로 추측해 볼 뿐이다.
여기서 역을 추론해 볼 수 있는 것은 View Merging 이 원천적으로 불가능한 것이 아니라 Optimizer 의 어떤 판단에 의해 View Merging 이 이루어지지 않았다는 것이다.
따라서 다음과 같이 MERGE Hint 를 강제로 부여하면 성공적으로 View Merging 이 이루어진다.

{section}
{column:width=50}


-- Need Check --
SQL> SELECT /*+ gather_plan_statistics */
  2    t1.c1, v.c2
  3  FROM
  4    t1,
  5    (SELECT /*+ merge */ c1, c3, COUNT(*) AS c2
  6      FROM t2
  7      GROUP BY c1, c3) v
  8  WHERE
  9    t1.c1 = v.c1 AND
 10    v.c3 = 1
 11  ;
       401          1
       201          1
       301          1
         1          1
       801          1
       901          1
       701          1
       101          1
       501          1
       601          1
10 개의 행이 선택되었습니다.

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'allstats cost last'));
SQL_ID  dxz4b1bbuym82, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */   t1.c1, v.c2 FROM   t1,   (SELECT
/*+ merge */ c1, c3, COUNT(*) AS c2     FROM t2     GROUP BY c1, c3) v
WHERE   t1.c1 = v.c1 AND   v.c3 = 1

-- Oracle 11g
----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |       |      1 |        |    12 (100)|     10 |00:00:00.02 |      32 |       |       |          |
|   1 |  HASH GROUP BY         |       |      1 |     10 |    12  (17)|     10 |00:00:00.02 |      32 |   781K|   781K| 1002K (0)|
|*  2 |   HASH JOIN            |       |      1 |     10 |    11  (10)|     10 |00:00:00.02 |      32 |   968K|   968K| 1080K (0)|
|*  3 |    TABLE ACCESS FULL   | T2    |      1 |     10 |     3   (0)|     10 |00:00:00.01 |       7 |       |       |          |
|   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")
   3 - filter("C3"=1)

-- Oracle 10g
----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------
|   1 |  HASH GROUP BY         |       |      1 |     10 |    11  (19)|     10 |00:00:00.01 |      30 |       |       |          |
|*  2 |   HASH JOIN            |       |      1 |     10 |    10  (10)|     10 |00:00:00.01 |      30 |   968K|   968K| 1017K (0)|
|*  3 |    TABLE ACCESS FULL   | T2    |      1 |     10 |     3   (0)|     10 |00:00:00.01 |       6 |       |       |          |
|   4 |    INDEX FAST FULL SCAN| T1_N1 |      1 |  10000 |     6   (0)|  10000 |00:00:00.01 |      24 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------

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

   2 - access("T1"."C1"="C1")
   3 - filter("C3"=1)

-- 2 교재에서는 NESTED LOOPS 사용
-- 4 교재에서는 INDEX RANGE SCAN 사용

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

MERGE Hint 를 이용해 강제로 View Merging 을 수행한 경우 Cost 는 13 에서 15로 증가하지만 실제 일량은 28에서 19로 감소하는 것을 알 수 있다.
Optimizer 의 판단이 항상 옳은 것은 아니라는 것을 단적으로 알 수 있는 좋은 예제이다.

문서에 대하여

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

Non-Mergable Views (by sunshiny) [2011.04.29]

| Non-Mergeable views |

문서에 대하여