| Simple View Merging |

Simple View 는 말 그대로 Group By 등의 복잡한 Operation 을 포함하지 않는 단순한 View를 의미한다.
Simple View 조차도 Merging 의 성공 여부에 따라 실행 계획 상의 큰 차이를 보일 수 있다.

간단한 예제를 통해 Simple View Merging 의 동작 방식을 알아보자.
우선 다음과 같이 필요한 Object 를 생성한다.

{section}
{column:width=50}


Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

SQL> drop table t1 purge;
테이블이 삭제되었습니다.
SQL> drop table t2 purge;
테이블이 삭제되었습니다.
SQL> drop table t3 purge;
테이블이 삭제되었습니다.
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> create index t1_n2 on t1(c3);
인덱스가 생성되었습니다.
SQL> create index t2_n2 on t2(c3);
인덱스가 생성되었습니다.
SQL> create index t3_n2 on t3(c3);
인덱스가 생성되었습니다.

-- 논의를 간단하게 하기 위해 다음과 같이 Cost Based Query Transformation 을 비활성화 한다.
SQL> ALTER SESSION SET "_optimizer_cost_based_transformation" = off;
세션이 변경되었습니다.
SQL> ALTER SESSION SET "_optimizer_push_pred_cost_based" = false;
세션이 변경되었습니다.

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

Column c1 은 Unique 값을 가지며, Column c2 는 하나의 Distinct Count 만, Column c3 는 0~99 의 100개의 Distinct Count 를 갖도록 Data 를 생성한다.
Table t1 은 10,000 건, Table t2 는 1,000 건, Table t3 는 100 건의 Data 를 갖는다.

{section}
{column:width=50}



SQL> insert into t1
  2  select level, 'dummy', mod(level, 100) from dual
  3  connect by level <= 10000
  4  ;
10000 개의 행이 만들어졌습니다.

SQL> insert into t2
  2  select level, 'dummy', mod(level, 100)  from dual
  3  connect by level <= 1000
  4  ;
1000 개의 행이 만들어졌습니다.

SQL> insert into t3
  2  select level, 'dummy', mod(level, 100) from dual
  3  connect by level <= 100
  4  ;
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 처리가 정상적으로 완료되었습니다.

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

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

{section}
{column:width=50}



SQL> SELECT count(*)
  2  FROM (
  3  SELECT /*+ gather_plan_statistics */
  4    t1.c1, v.c2
  5  FROM
  6    t1,
  7    (SELECT /*+ no_merge */ c1, c2
  8      FROM t2
  9      WHERE c1 BETWEEN 1 AND 1000) v
 10  WHERE
 11    t1.c1 = v.c1
 12  )
 13  ;
      1000

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'allstats cost last'));
SQL_ID  gp8tnk7qwgcj8, child number 0
-------------------------------------
SELECT count(*) FROM ( SELECT /*+ gather_plan_statistics */   t1.c1,
v.c2 FROM   t1,   (SELECT /*+ no_merge */ c1, c2     FROM t2     WHERE
c1 BETWEEN 1 AND 1000) v WHERE   t1.c1 = v.c1 )

Plan hash value: 3590297760

-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem|
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |       |      1 |        |    10 (100)|      1 |00:00:00.02 |      33 |       |       ||
|   1 |  SORT AGGREGATE         |       |      1 |      1 |            |      1 |00:00:00.02 |      33 |       |       ||
|*  2 |   HASH JOIN             |       |      1 |   1000 |    10  (10)|   1000 |00:00:00.02 |      33 |  1066K|  1066K| 1192K (0)|
|   3 |    VIEW                 |       |      1 |   1000 |     2   (0)|   1000 |00:00:00.01 |       8 |       |       ||
|*  4 |     INDEX FAST FULL SCAN| T2_N1 |      1 |   1000 |     2   (0)|   1000 |00:00:00.01 |       8 |       |       ||
|   5 |    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"="V"."C1")
   4 - filter(("C1">=1 AND "C1"<=1000)) -- 교재에서는 access 처리 : INDEX RANGE SCAN 사용

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

실행 계혹의 3번 단계의 VIEW Operation이 View Merging이 실패 했다는 것을 잘 보여준다.
View Merging 이 실패한 경우 Oracle 은 View 에 대한 Optimization 작업과 전체 Query 에 대한 Optimization 작업을 별개로 처리하게 된다.
Oracle 의 Optimization 작업은 Query Block 단위로 이루어지기 때문이다.
위의 결과를 보면 Inline View 는 Index Range Scan 을 수행하게끔 Optimization 이 이루어지고,
Main Query는 View 의 결과를 Index t1_n1에 대한 Index Fast Full Scan과 Hash Join을 수행하게끔 Optimization 이 이루어진다.
View Merging 에 의해 Inline View 가 Main Query 안으로 흡수되지 못하는 상황에서는 선택 가능한 최적의 실행 계획인 셈이다.

View Merging 이 성공적으로 이루어진 경우에는 전혀 다른 실행 계획을 보인다.
{section}
{column:width=50}



SQL> SELECT count(*)
  2  FROM (
  3  SELECT /*+ gather_plan_statistics */
  4    t1.c1, v.c2
  5  FROM
  6    t1,
  7    (SELECT c1, c2
  8      FROM t2
  9      WHERE c1 BETWEEN 1 AND 1000) v
 10  WHERE
 11    t1.c1 = v.c1
 12  )
 13  ;
      1000

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'allstats cost last'));
SQL_ID  gc3pxkdxw3gvr, child number 0
-------------------------------------
SELECT count(*) FROM ( SELECT /*+ gather_plan_statistics */   t1.c1,
v.c2 FROM   t1,   (SELECT c1, c2     FROM t2     WHERE c1 BETWEEN 1 AND
1000) v WHERE   t1.c1 = v.c1 )

Plan hash value: 856841256

----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |       |      1 |        |     6 (100)|      1 |00:00:00.01 |      11 |       |       |          |
|   1 |  SORT AGGREGATE        |       |      1 |      1 |            |      1 |00:00:00.01 |      11 |       |       |          |
|*  2 |   HASH JOIN            |       |      1 |    999 |     6  (17)|   1000 |00:00:00.01 |      11 |  1066K|  1066K| 1241K (0)|
|*  3 |    INDEX FAST FULL SCAN| T2_N1 |      1 |   1000 |     2   (0)|   1000 |00:00:00.01 |       8 |       |       |          |
|*  4 |    INDEX RANGE SCAN    | T1_N1 |      1 |   1000 |     3   (0)|   1000 |00:00:00.01 |       3 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------

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

   2 - access("T1"."C1"="C1")
   3 - filter(("C1">=1 AND "C1"<=1000)) -- 교재에서는 access 처리 : INDEX RANGE SCAN 사용
   4 - access("T1"."C1">=1 AND "T1"."C1"<=1000)

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

우선 가장 큰 변화는 실행 계획에서 View Operation 이 없어졌다는 것이다.
View Merging으로 인해 View 가 없어졌다는 것을 의미한다.
또 하나의 큰 변화는 Table t1, t2 에 대해 모두 Index Range Scan 을 사용하게 되었다는 것이다.(11g 테스트에서는 INDEX FAST FULL SCAN 사용)
그로 인해 Cost 뿐만 아니라 실제 일량도 크게 개선되었다.
왜 이런 변화가 생긴 것인가? Predicate Information 에 해답이 있다.

4번 단계에서 사용된 Predicate 를 보면 놀라운 사실을 알 수 있다.
이 Predicate 는 우리가 사용한 적이 없다.
Oracle 에 의해 추가적으로 생성된 Predicate 인 셈이다.
이 Predicate 의 존재로 인해 Table t2 뿐만 아니라 Table t1 에 대해서도 Index Range Scan 을 사용할 수 있게 되었다.
이를 역으로 추론해 보면 Simple View Merging 에 의해 원래 Query 가 다음과 같이 변형되었다는 것을 알 수 있다.

{section}
{column:width=50}



SELECT 
	t1.c1, t2.c2
FROM
	t1, t2
WHERE 
	t1.c1 = t2.c1 AND
	t1.c1 BETWEEN 1 AND 1000 AND
	t2.c1 BETWEEN 1 AND 1000
;

       998 dummy
       999 dummy
      1000 dummy

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

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'allstats cost last'));
SQL_ID  ad7gwucmhnq9z, child number 0
-------------------------------------
SELECT  t1.c1, t2.c2 FROM  t1, t2 WHERE  t1.c1 = t2.c1 AND  t1.c1
BETWEEN 1 AND 1000 AND  t2.c1 BETWEEN 1 AND 1000

Plan hash value: 1733838511

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |      1 |        |     7 (100)|   1000 |00:00:00.01 |      77 |    |          |          |
|*  1 |  HASH JOIN         |       |      1 |    999 |     7  (15)|   1000 |00:00:00.01 |      77 |  1066K|  1066K| 1225K (0)|
|*  2 |   INDEX RANGE SCAN | T1_N1 |      1 |   1000 |     3   (0)|   1000 |00:00:00.01 |       3 |    |          |          |
|*  3 |   TABLE ACCESS FULL| T2    |      1 |   1000 |     3   (0)|   1000 |00:00:00.01 |      74 |    |          |          |
------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."C1"="T2"."C1")
   2 - access("T1"."C1">=1 AND "T1"."C1"<=1000)
   3 - filter(("T2"."C1">=1 AND "T2"."C1"<=1000))

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

문서에 대하여

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

Simple View Merging (by sunshiny) [2011.04.28]

| Simple View Merging |

문서에 대하여