03.뷰 Merging

뷰머징이란

뷰머징

<쿼리1>
select *
  from (select * from emp where job = 'SALESMAN') a
     , (select * from dept where loc = 'CHICAGO') b
 where a.deptno = b.deptno

위와 같은 쿼리는 사람이 보기에는 읽기 편하지만
최적화를 수행하는 옵티마이저에게는 좋지 않은 쿼리이다.
옵티마이저는 가급적 위와 같은 쿼리 블록을 아래 쿼리처럼 풀어내려는 습성이 있다.

<쿼리2>
select *
  from emp a, dept b
 where a.deptno = b.deptno
   and a.job = 'SALESMAN'
   and b.loc = 'CHICAGO'

<쿼리1>의 뷰 쿼리 블록은 액세스 쿼리 블록과의 머지 과정을 거쳐 <쿼리2>와 같은 형태로 변환되는데 이를 뷰머징이라고 한다.
이와 같이 쿼리가 풀어지면 옵티마이저는 더욱 다양한 액세스 경로를 탐색할 수 있게 된다.

단순 뷰(Simple View) Merging

  • 조건절과 조인문만을 포함하는 단순 뷰는 no_merge 힌트를 사용하지 않는 한 언제든 merge가 일어난다.

예시

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

아래와 같은 쿼리를 뷰머징하지 않는다면

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         | E-Rows |
--------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |        |
|   1 |  NESTED LOOPS                |              |        |
|   2 |   NESTED LOOPS               |              |      1 |
|   3 |    VIEW                      | EMP_SALESMAN |      1 | -> 뷰 그대로 사용
|*  4 |     TABLE ACCESS FULL        | EMP          |      1 |
|*  5 |    INDEX UNIQUE SCAN         | DEPT_PK      |      1 |
|   6 |   TABLE ACCESS BY INDEX ROWID| DEPT         |      1 |
--------------------------------------------------------------

뷰머징을 해준다면..

select /*+ 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    | E-Rows |
---------------------------------------------------------
|   0 | SELECT STATEMENT             |         |        |
|   1 |  NESTED LOOPS                |         |        |
|   2 |   NESTED LOOPS               |         |      1 |
|*  3 |    TABLE ACCESS FULL         | EMP     |      1 |
|*  4 |    INDEX UNIQUE SCAN         | DEPT_PK |      1 |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |
---------------------------------------------------------


복합 뷰(Complex View) Merging

  • group by 절, select 절에 distinct 연산자를 포함하는 복합 뷰는 \_complex_view_merging 파라미터를 true로 설정할 때만 merging이 일어난다.
  • \_complex_view_merging 파라메터 버전에 따른 변화
    • 8i : default 값이 false \-> merge힌트를 사용해야 merge 시도한다.
    • 9i이후 : default 값이 true \-> no_merge힌트를 사용해야 merge 안 한다.

예시

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           |
-----------------------------
|   0 | SELECT STATEMENT    |
|   1 |  HASH GROUP BY      |
|*  2 |   HASH JOIN         |
|*  3 |    TABLE ACCESS FULL|
|   4 |    TABLE ACCESS FULL|
-----------------------------

-> 뷰를 해체하여 일반 조인으로 변경하였다.

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

-------------------------------
| Id  | Operation             |
-------------------------------
|   0 | SELECT STATEMENT      |
|*  1 |  HASH JOIN            |
|   2 |   JOIN FILTER CREATE  |
|*  3 |    TABLE ACCESS FULL  |
|   4 |   VIEW                |
|   5 |    HASH GROUP BY      |
|   6 |     JOIN FILTER USE   |
|*  7 |      TABLE ACCESS FULL|
-------------------------------

  • 아래 항목을 포함할 때는 merge가 불가능하다.
    • 집합 연산자(union, uion all, minus)
    • connect by 절
    • rownum pseudo 컬럼
    • select-list에 집계 함수(avg, count, max, min, sum) 사용 : group by 없지 전체 집계하는 경우
    • 분석함수

테스트 : 뷰머징 불가능 케이스

--집합연산의 경우

select d.dname
  from dept d
    , (select *
         from emp
        where job = 'MANAGER'
       union all
       select *
         from emp
        where job = 'SALESMAN') e
 where d.deptno = e.deptno
   and d.loc = 'CHICAGO'

------------------------------
| Id  | Operation            |
------------------------------
|   0 | SELECT STATEMENT     |
|*  1 |  HASH JOIN           |
|*  2 |   TABLE ACCESS FULL  |
|   3 |   VIEW               |
|   4 |    UNION-ALL         |
|*  5 |     TABLE ACCESS FULL|
|*  6 |     TABLE ACCESS FULL|
------------------------------

merge가 되지 않는다.


--connect by 사용한 경우

select d.dname
  from dept d
    , (select
         from emp
        start with mgr is null
        connect by nocycle prior empno = mgr
      ) e
 where d.deptno = e.deptno
   and d.loc = 'CHICAGO'

---------------------------------------------------
| Id  | Operation                                 |
---------------------------------------------------
|   0 | SELECT STATEMENT                          |
|*  1 |  HASH JOIN                                |
|*  2 |   TABLE ACCESS FULL                       |
|   3 |   VIEW                                    |
|*  4 |    CONNECT BY NO FILTERING WITH START-WITH|
|   5 |     TABLE ACCESS FULL                     |
---------------------------------------------------

-->merge 안 됨

select d.dname
  from dept d
    , (select /*+merge*/*
         from emp
        start with mgr is null
        connect by nocycle prior empno = mgr
      ) e
 where d.deptno = e.deptno
   and d.loc = 'CHICAGO'

---------------------------------------------------
| Id  | Operation                                 |
---------------------------------------------------
|   0 | SELECT STATEMENT                          |
|*  1 |  HASH JOIN                                |
|*  2 |   TABLE ACCESS FULL                       |
|   3 |   VIEW                                    |
|*  4 |    CONNECT BY NO FILTERING WITH START-WITH|
|   5 |     TABLE ACCESS FULL                     |
---------------------------------------------------

-->hint를 사용해도 안 됨


--rownum을 사용한 경우

select d.dname, e.empno
  from dept d
    , (select /*+merge*/rownum, deptno, empno
         from emp) e
 where d.deptno = e.deptno
   and d.loc = 'CHICAGO'

-------------------------------------
| Id  | Operation            | Name |
-------------------------------------
|   0 | SELECT STATEMENT     |      |
|*  1 |  HASH JOIN           |      |
|*  2 |   TABLE ACCESS FULL  | DEPT |
|   3 |   VIEW               |      |
|   4 |    COUNT             |      |
|   5 |     TABLE ACCESS FULL| EMP  |
-------------------------------------

-->역시 안 됨


--전체 집계함수를 사용한 경우

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

--------------------------------------
| Id  | Operation                    |
--------------------------------------
|   0 | SELECT STATEMENT             |
|   1 |  NESTED LOOPS                |
|   2 |   VIEW                       |
|   3 |    SORT AGGREGATE            |
|   4 |     TABLE ACCESS FULL        |
|*  5 |   TABLE ACCESS BY INDEX ROWID|
|*  6 |    INDEX UNIQUE SCAN         |
--------------------------------------


--집계함수 사용한 경우

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

------------------------------
| Id  | Operation            |
------------------------------
|   0 | SELECT STATEMENT     |
|*  1 |  HASH JOIN           |
|*  2 |   TABLE ACCESS FULL  |
|   3 |   VIEW               |
|   4 |    WINDOW SORT       |
|   5 |     TABLE ACCESS FULL|
------------------------------

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

  • 9i에서 뷰를 무조건 머징하였고, 대부분의 경우 뷰머징은 더 나은 성능을 보일 때가 많지만 그렇지 않은 경우도 있었다.
    따라서 10g부터는 비용기반 쿼리 변환 방식으로 전환되었다.
  • 기능을 제어하기 위한 파라미터 \_optimizer_cost_based_transformation
    • on
    • off
    • exhaustive
    • linear
    • iterative
  • 조건절 pushing도 비용기반 쿼리 방식으로 변환 되었지만 파라미터는 다르다.(optimizer_push_pred_cost_based)
  • opt_param 힌트를 통해 쿼리 레벨에서도 조정 가능하다.
    select /*\+ opt_param('_optimizer_push_pred_cost_based','false') \*/ from ..

Merging 되지 않은 뷰의 처리 방식

  • 뷰 Merging이 비용이 증가한다고 판단하거나 부정확한 결과 집합을 만들 가능성이 있으면
    뷰머징을 포기하고, 조건절 Pushing을 시도한다. 이마저도 실패하면 개별 블록 최적화한다.

예시

-- 단순뷰였을 때 Merging 실행계획
select /*+ leading(e) use_nl(d) */ *
from dept d
  , (select * from emp) e
where e.deptno = d.deptno

--------------------------------------------
| Id  | Operation          | Name | E-Rows |
--------------------------------------------
|   0 | SELECT STATEMENT   |      |        |
|   1 |  NESTED LOOPS      |      |     14 |
|   2 |   TABLE ACCESS FULL| EMP  |     14 |
|*  3 |   TABLE ACCESS FULL| DEPT |      1 |
--------------------------------------------

-- no_merge문을 사용했을 때 실행계획
select /*+ leading(e) use_nl(d) */ *
from dept d
  , (select /*+no_merge*/* from emp) e
where e.deptno = d.deptno

---------------------------------------------
| Id  | Operation           | Name | E-Rows |
---------------------------------------------
|   0 | SELECT STATEMENT    |      |        |
|   1 |  NESTED LOOPS       |      |     14 |
|   2 |   VIEW              |      |     14 |
|   3 |    TABLE ACCESS FULL| EMP  |     14 |
|*  4 |   TABLE ACCESS FULL | DEPT |      1 |
---------------------------------------------

'VIEW'라고 표시되었다고 해서 중간집합을 만드는 것은 아니다!
위와 같은 실행계획에서도 부분범위 처리가 가능하다!

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

----------------------------------------------------------------------------
| Id  | Operation                     | Name    | Starts | E-Rows | A-Rows |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |      1 |        |      1 |
|*  1 |  COUNT STOPKEY                |         |      1 |        |      1 |
|   2 |   NESTED LOOPS                |         |      1 |        |      1 |
|   3 |    NESTED LOOPS               |         |      1 |      1 |      1 |
|   4 |     VIEW                      |         |      1 |      1 |      1 |
|   5 |      TABLE ACCESS FULL        | EMP     |      1 |     14 |      1 |
|*  6 |     INDEX UNIQUE SCAN         | DEPT_PK |      1 |      1 |      1 |-->부분범위 처리하고 있음
|   7 |    TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      1 |      1 |
----------------------------------------------------------------------------

view라고 표시된 오퍼레이션 단계가 추가되었다고 해서 다음 단계로 넘어가기 전에 중간집합을 생성하는 것은 아님을 알 수 있다.

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

--------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |     14 |00:00:00.01 |      12 |
|   1 |  NESTED LOOPS       |      |      1 |     14 |     14 |00:00:00.01 |      12 |
|   2 |   TABLE ACCESS FULL | DEPT |      1 |      4 |      4 |00:00:00.01 |       3 |
|*  3 |   VIEW              |      |      4 |      4 |     14 |00:00:00.01 |       9 |
|   4 |    TABLE ACCESS FULL| EMP  |      4 |     14 |     56 |00:00:00.01 |       9 |
--------------------------------------------------------------------------------------

뷰가 inner table로 액세스될 때도 view 처리 단계에서 중간 집합을 생성하지 않는다.
dept에서 읽은만큼 emp 테이블을 fullscan하고 있다.
(총 buffer = 9이고 출력 건수는 56)

inline view에 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

------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |      1 |        |     14 |00:00:00.01 |       5 |       |       |          |
|   1 |  NESTED LOOPS        |      |      1 |     14 |     14 |00:00:00.01 |       5 |       |       |          |
|   2 |   TABLE ACCESS FULL  | DEPT |      1 |      4 |      4 |00:00:00.01 |       3 |       |       |          |
|*  3 |   VIEW               |      |      4 |      4 |     14 |00:00:00.01 |       2 |       |       |          |
|   4 |    SORT ORDER BY     |      |      4 |     14 |     56 |00:00:00.01 |       2 |  2048 |  2048 | 2048  (0)|
|   5 |     TABLE ACCESS FULL| EMP  |      1 |     14 |     14 |00:00:00.01 |       2 |       |       |          |
------------------------------------------------------------------------------------------------------------------

inline view에 order by를 추가했더니..
emp 테이블을 액세스하는 단계에서 14개의 row만 접근하였고 buffer 사용도 9->2로 줄었다.
sort를 위해 memory 사용량은 늘어났다.
emp 테이블을 한번만 full scan했고 소트 수행 후 PGA에 저장된 중간집합을 반복 액세스한 것을 알 수 있다.