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

03. 뷰 Mergin

(1)뷰 Merging 이란?



<쿼리1>
SQL> SELECT * 
     FROM ( SELECT * FROM EMP WHERE JOB = 'SALESMAN' ) A
         ,( SELECT * FROM DEPT WHERE LOC = 'CHICAGO' ) B
     WHERE A.DEPTNO = B.DEPTNO;

 
----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |    57 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |     1 |    57 |     4   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL          | EMP     |     3 |   111 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   2 - filter("JOB"='SALESMAN')
   3 - filter("LOC"='CHICAGO')
   4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")



<쿼리2>
SQL> SELECT * FROM EMP A, DEPT B
  2   WHERE A.DEPTNO = B.DEPTNO
  3     AND A.JOB = 'SALESMAN'
  4     AND B.LOC = 'CHICAGO';

 

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |    57 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |     1 |    57 |     4   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL          | EMP     |     3 |   111 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   2 - filter("A"."JOB"='SALESMAN')
   3 - filter("B"."LOC"='CHICAGO')
   4 - access("A"."DEPTNO"="B"."DEPTNO")


<쿼리1>의 뷰 쿼리 블록은 액세스 쿼리 블록(뷰를 참조하는 쿼리 블록)과의 머지과정을 거쳐 <쿼리2>와 같은 형태로 변환되는데, 이를 '뷰 Merging'이라고 한다.

-이 기능을 제어하는 힌트로는 merge와 no_merge가 있다.


SQL> SELECT /*+ no_merge( A ) no_merge( B ) */ *
       FROM ( SELECT * FROM EMP WHERE JOB = 'SALESMAN' ) A
           ,( SELECT * FROM DEPT WHERE LOC = 'CHICAGO' ) B
      WHERE A.DEPTNO = B.DEPTNO;

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |   117 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN          |      |     1 |   117 |     7  (15)| 00:00:01 |
|   2 |   VIEW              |      |     1 |    30 |     3   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| DEPT |     1 |    20 |     3   (0)| 00:00:01 |
|   4 |   VIEW              |      |     3 |   261 |     3   (0)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL| EMP  |     3 |   111 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   1 - access("A"."DEPTNO"="B"."DEPTNO")
   3 - filter("LOC"='CHICAGO')
   5 - filter("JOB"='SALESMAN')

-같은 실행계획으로써 수행되며, 쿼리 성능에 하등 차이가 없다.

(2)단순 뷰(Simple View) Merging

조건절과 조인문만을 포함하는 단순 뷰(Simple View)는 no_merge힌트를 사용하지 않는 한 언제든 Merging이 일어난다.



SQL> create or replace view emp_salesman
     as
     select empno, ename, job, mgr, hiredate, sal, comm, deptno
       from emp
     where job = 'SALESMAN';

뷰가 생성되었습니다.




SQL> select e.empno, e.ename, e.job, e.mgr, e.sal, d.dname
       from emp_salesman e, dept d
      where d.deptno = e.deptno
        and e.sal >= 1500;

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |     3 |   126 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |             |     3 |   126 |     3   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| EMP         |     3 |    87 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | EMP_SAL_IDX |    13 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| DEPT        |     1 |    13 |     1   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN         | PK_DEPT     |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

   2 - filter("JOB"='SALESMAN')
   3 - access("SAL">=1500)
   5 - access("D"."DEPTNO"="DEPTNO")




SQL>select /*+ no_merge( e ) */
          e.empno, e.ename, e.job, e.mgr, e.sal, d.dname
      from emp_salesman e, dept d
     where d.deptno = e.deptno
       and e.sal >= 1500;

----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |     3 |   234 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                 |              |     3 |   234 |     3   (0)| 00:00:01 |
|   2 |   VIEW                        | EMP_SALESMAN |     3 |   195 |     2   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| EMP          |     3 |    87 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | EMP_SAL_IDX  |    13 |       |     1   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID | DEPT         |     1 |    13 |     1   (0)| 00:00:01 |
|*  6 |    INDEX UNIQUE SCAN          | PK_DEPT      |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

   3 - filter("JOB"='SALESMAN')
   4 - access("SAL">=1500)
   6 - access("D"."DEPTNO"="E"."DEPTNO")

(3)복합 뷰(Complex View) Merging

group by절, select-list에 distinct연산자 포함하는 복합 뷰는 _complex_view_merging파라미터를 true로 설정할 때만 Merging이 일어난다.
10g에서는 복합 뷰 Merging을 일단 시도하지만, 원본 쿼리에 대해서도 비용을 같이 계산해 Merging했을 때의 비용이 더 낮을 때만 그것을 채택한다.(비용기반 쿼리 변환)
complex_view_merging파라미터를 true로 설정하더라도 아래 항목들을 포함하는 복합 뷰는 Merging될 수 없다.
1. 집합(set)연산자( union, union all, intersect, minus )
2. connect by절
3. ROWNUM pseudo 컬럼
4. select-list에 집계 함수(avg, count, max, min, sum)사용 : group by 없이 전체를 집계하는 경우를 말함
5. 분석 함수



SQL> select d.dname,avg_sal_dept
       from dept d
           ,( select deptno, avg(sal) avg_sal_dept from emp group by deptno ) e
      where d.deptno = e.deptno
        and d.loc= 'CHICAGO'

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |    46 |     5  (20)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |     1 |    46 |     5  (20)| 00:00:01 |
|   2 |   VIEW                       |         |     3 |    78 |     4  (25)| 00:00:01 |
|   3 |    HASH GROUP BY             |         |     3 |    21 |     4  (25)| 00:00:01 |
|   4 |     TABLE ACCESS FULL        | EMP     |    14 |    98 |     3   (0)| 00:00:01 |
|*  5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |
|*  6 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   5 - filter("D"."LOC"='CHICAGO')
   6 - access("D"."DEPTNO"="E"."DEPTNO")




SQL> select /*+ merge(e) */
         d.dname,avg_sal_dept
       from dept d
           ,( select deptno, avg(sal) avg_sal_dept from emp group by deptno ) e
      where d.deptno = e.deptno
        and d.loc= 'CHICAGO'

-----------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |     3 |    81 |     5  (20)| 00:00:01 |
|   1 |  HASH GROUP BY                |         |     3 |    81 |     5  (20)| 00:00:01 |
|   2 |   NESTED LOOPS                |         |     5 |   135 |     4   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL          | EMP     |    14 |    98 |     3   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |
|*  5 |     INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   4 - filter("D"."LOC"='CHICAGO')
   5 - access("D"."DEPTNO"="DEPTNO")


SQL> select /*+ no_merge(e) */
          d.dname,avg_sal_dept
      from dept d
          ,( select deptno, avg(sal) avg_sal_dept from emp group by deptno ) e
     where d.deptno = e.deptno
       and d.loc= 'CHICAGO'



----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |    46 |     5  (20)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |     1 |    46 |     5  (20)| 00:00:01 |
|   2 |   VIEW                       |         |     3 |    78 |     4  (25)| 00:00:01 |
|   3 |    HASH GROUP BY             |         |     3 |    21 |     4  (25)| 00:00:01 |
|   4 |     TABLE ACCESS FULL        | EMP     |    14 |    98 |     3   (0)| 00:00:01 |
|*  5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |
|*  6 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   5 - filter("D"."LOC"='CHICAGO')
   6 - access("D"."DEPTNO"="E"."DEPTNO")

(4)비용기반 쿼리 변환의 필요성



select d.dname,avg_sal_dept
  from dept d
      ,( select deptno, avg(sal) avg_sal_dept from emp group by deptno ) e
 where d.deptno = e.deptno
   and d.loc= 'CHICAGO';

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |    46 |     5  (20)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |     1 |    46 |     5  (20)| 00:00:01 |
|   2 |   VIEW                       |         |     3 |    78 |     4  (25)| 00:00:01 |
|   3 |    HASH GROUP BY             |         |     3 |    21 |     4  (25)| 00:00:01 |
|   4 |     TABLE ACCESS FULL        | EMP     |    14 |    98 |     3   (0)| 00:00:01 |
|*  5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |
|*  6 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   5 - filter("D"."LOC"='CHICAGO')
   6 - access("D"."DEPTNO"="E"."DEPTNO")


뷰 Merging했을 때의 성패는 loc = 'CHICAGO' 조건에 달렸다. 이 조건에 의해 선택된 deptno가 emp테이블에서 많은 비중을 차지한다면 오히려 Table Full 
Scan을 감수하더라도 group by로 먼저 집합을 줄이고 나서 조인하는 편이 더 나을 것이다.

10g부터는 비용기반 쿼리 변환 방식으로 전환하게 되었고, 이 기능을 제어하기 위한 파라미터가 _optimizer_cost_based_transformation이다.
설정 가능한 값으로는 5가지(on, off, exhaustive, linear, iterative)가 있다.( Unnesting도 이 파라미터에 의해 영향을 받음)
조건절( Predicate ) Pushing은 _optimizer_push_pred_cost_based로 제어
10.2.0.2부터 connect by은 _optimizer_connect_by_cost_based로 제어
opt_param힌트(10gR2부터 제공)를 이용해 아래와 같이 쿼리 레벨에서 파라미터를 변경할 수도 있다.



 select /*+ opt_param( '_optimizer_cost_based_transformation', 'off') */
        d.dname,avg_sal_dept
   from dept d
       ,( select deptno, avg(sal) avg_sal_dept from emp group by deptno ) e
  where d.deptno = e.deptno
    and d.loc= 'CHICAGO'


----------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |     3 |    81 |     5  (20)| 00:00:01 |
|   1 |  HASH GROUP BY                |         |     3 |    81 |     5  (20)| 00:00:01 |
|   2 |   NESTED LOOPS                |         |     5 |   135 |     4   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL          | EMP     |    14 |    98 |     3   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |
|*  5 |     INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   4 - filter("D"."LOC"='CHICAGO')
   5 - access("D"."DEPTNO"="DEPTNO")



SQL> select /*+ opt_param( '_optimizer_cost_based_transformation', 'on') */
           d.dname,avg_sal_dept
       from dept d
           ,( select deptno, avg(sal) avg_sal_dept from emp group by deptno ) e
      where d.deptno = e.deptno
        and d.loc= 'CHICAGO'



----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |    46 |     5  (20)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |     1 |    46 |     5  (20)| 00:00:01 |
|   2 |   VIEW                       |         |     3 |    78 |     4  (25)| 00:00:01 |
|   3 |    HASH GROUP BY             |         |     3 |    21 |     4  (25)| 00:00:01 |
|   4 |     TABLE ACCESS FULL        | EMP     |    14 |    98 |     3   (0)| 00:00:01 |
|*  5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |
|*  6 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   5 - filter("D"."LOC"='CHICAGO')
   6 - access("D"."DEPTNO"="E"."DEPTNO")

(5)Merging 되지 않은 뷰의 처리방식



단순 뷰를 참조하므로 버전에 상관없이 항상 뷰 Merging이 일어 난다.


select /*+ leading(e) use_nl(d) */ *
  from dept d
      ,(select * from emp ) e
where e.deptno = d.deptno

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0         24          0          14
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.01          0         24          0          14

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54  

Rows     Row Source Operation
-------  ---------------------------------------------------
     14  NESTED LOOPS  (cr=24 pr=0 pw=0 time=136 us)
     14   TABLE ACCESS FULL EMP (cr=8 pr=0 pw=0 time=275 us)
     14   TABLE ACCESS BY INDEX ROWID DEPT (cr=16 pr=0 pw=0 time=510 us)
     14    INDEX UNIQUE SCAN PK_DEPT (cr=2 pr=0 pw=0 time=210 us)(object id 51150)



no_merge힌트를 사용해 뷰 Merging을 방지했을 때의 SQL트레이스 결과

select /*+ leading(e) use_nl(d) */ *
  from dept d
      ,(select /*+ NO_MERGE */ * from emp ) e
where e.deptno = d.deptno

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0         24          0          14
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0         24          0          14

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54  

Rows     Row Source Operation
-------  ---------------------------------------------------
     14  NESTED LOOPS  (cr=24 pr=0 pw=0 time=148 us)
     14   VIEW  (cr=8 pr=0 pw=0 time=404 us)
     14    TABLE ACCESS FULL EMP (cr=8 pr=0 pw=0 time=285 us)
     14   TABLE ACCESS BY INDEX ROWID DEPT (cr=16 pr=0 pw=0 time=512 us)
     14    INDEX UNIQUE SCAN PK_DEPT (cr=2 pr=0 pw=0 time=209 us)(object id 51150)

실행계획에 'VIEW'라고 표시된 오퍼레이션 단계가 추가되었다고 해서 다음 단계로 넘어가기
전에 중간집합을 생성하는 것은 아니라는 점이다. 따라서 실행계획이 위와 같더라도 완전한
부분범위 처리가 가능하다.


뷰가 NL조인에서 Inner테이블로서 액세스될 때

select /*+ leading(d) use_nl(e) */ *
  from dept d
      ,(select /*+ NO_MERGE */ * from emp ) e
where e.deptno = d.deptno

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0         37          0          14
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0         37          0          14

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54  

Rows     Row Source Operation
-------  ---------------------------------------------------
     14  NESTED LOOPS  (cr=37 pr=0 pw=0 time=169 us)
      4   TABLE ACCESS FULL DEPT (cr=8 pr=0 pw=0 time=105 us)
     14   VIEW  (cr=29 pr=0 pw=0 time=366 us)
     56    TABLE ACCESS FULL EMP (cr=29 pr=0 pw=0 time=429 us)

VIEW처리 단계에서 중간집합을 생성하지는 않는다. 따라서 드라이빙 테이블 DEPT에서 
읽은 건수만큼 EMP테이블에 대한 FULL SCAN을 반복한다. EMP테이블을 FULL SCAN하는 
단계에서 읽은 블록개수(=29)와 출력된 결과(=56)가 이를 잘 말해주고 있다.


ORDER BY절을 추가

select /*+ leading(d) use_nl(e) */ *
  from dept d
      ,(select /*+ NO_MERGE */ * from emp ORDER BY ENAME) e
where e.deptno = d.deptno

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0         15          0          14
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0         15          0          14

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54  

Rows     Row Source Operation
-------  ---------------------------------------------------
     14  NESTED LOOPS  (cr=15 pr=0 pw=0 time=230 us)
      4   TABLE ACCESS FULL DEPT (cr=8 pr=0 pw=0 time=115 us)
     14   VIEW  (cr=7 pr=0 pw=0 time=324 us)
     56    SORT ORDER BY (cr=7 pr=0 pw=0 time=342 us)
     14     TABLE ACCESS FULL EMP (cr=7 pr=0 pw=0 time=101 us)

EMP테이블 액세스 단계에서 14건만 리턴하고 블록 I/O도 7개로 줄어들었다. 
즉. EMP테이블은 한 번만 FULL SCAN했고, 소트 수행 후 PGA에 저장된 중간집합을 반복
액세스한 것을 알 수 있다. 따라서 추가적인 블록 I/O가 발생하지 않았다.

{*}이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법II'를 참고하였습니다.*

3. 뷰 Merging (by leechh1004) [2010.10.08]

03. 뷰 Mergin

(1)뷰 Merging 이란?

(2)단순 뷰(Simple View) Merging

(3)복합 뷰(Complex View) Merging

(4)비용기반 쿼리 변환의 필요성

(5)Merging 되지 않은 뷰의 처리방식