메뉴 건너뛰기

bysql.net

3. 뷰 Merging

2011.05.15 22:54

실천하자 조회 수:23376

03. 뷰 Merging


(1) 뷰 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> 형태로 변환 ☞ View Merging


  • View Merging 이유 : 옵티마이저가 더 다양한 액세스 경로를 조사대상으로 삼을 수 있음

  • View Merging 제어 힌트 : merge, no_merge

  • View Merging 이 일어나는 조건이면 <쿼리1>과 <쿼리2>의 쿼리 성능은 동일함 (100% 같은 실행계획 수행)



(2) 단순 뷰(Simple View) Merging

  • 조건절과 조인문만을 포함하는 단순 뷰(Simple View)일 경우, no_merge 힌트를 사용하지 않는 한 언제든 Merging 발생
  • group by, distinct 연산을 포함하는 복합뷰(Complex View)는 파라미터 설정 또는 힌트 사용에 의해서만 뷰 Merging 가능
  • 집합 연산자, connect by, rownum 등을 포함한 복합 뷰(Non-mergeable Views)뷰 Merging 불가능

<Simple View>

create or replace view emp_salesman as

select empno, ename, job, mgr, hiredate, sal, comm, deptno

from   emp

where job = 'SALESMAN';



<Simple View 뷰 No Merging 최적화>

SQL> select /*+ no_merge(e) */ e.empno, e.ename, e.job, e.mgr, e.sal, d.dname

  2  from   emp_salesman e, dept d

  3  where  d.deptno = e.deptno

  4  and    e.sal >= 1500 ;


      EMPNO ENAME      JOB              MGR        SAL DNAME

---------- ---------- --------- ---------- ---------- --------------

      7844 TURNER     SALESMAN        7698       1500 SALES

      7499 ALLEN      SALESMAN        7698       1600 SALES


Execution Plan

-----------------------------------------------------------------------------------------------

| Id  | Operation                      | Name         | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT               |              |     2 |   156 |     4   (0)| 00:00:01 |

|   1 |  NESTED LOOPS                  |              |       |       |            |          |

|   2 |   NESTED LOOPS                 |              |     2 |   156 |     4   (0)| 00:00:01 |

|   3 |    VIEW                        | EMP_SALESMAN |     2 |   130 |     2   (0)| 00:00:01 |

|*  4 |     TABLE ACCESS BY INDEX ROWID| EMP          |     2 |    58 |     2   (0)| 00:00:01 |

|*  5 |      INDEX RANGE SCAN          | EMP_SAL_IDX  |     8 |       |     1   (0)| 00:00:01 |

|*  6 |    INDEX UNIQUE SCAN           | PK_DEPT      |     1 |       |     0   (0)| 00:00:01 |

|   7 |   TABLE ACCESS BY INDEX ROWID  | DEPT         |     1 |    13 |     1   (0)| 00:00:01 |

-----------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):

---------------------------------------------------

   4 - filter("JOB"='SALESMAN')

   5 - access("SAL">=1500)

   6 - access("D"."DEPTNO"="E"."DEPTNO")



<Simple View 뷰 Merging 최적화>

SQL> select /*+ merge(e) */ e.empno, e.ename, e.job, e.mgr, e.sal, d.dname

  2  from   emp_salesman e, dept d

  3  where  d.deptno = e.deptno

  4  and    e.sal >= 1500 ;


Execution Plan

---------------------------------------------------------------------------------------------

| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |             |     2 |    84 |     4   (0)| 00:00:01 |

|   1 |  NESTED LOOPS                 |             |       |       |            |          |

|   2 |   NESTED LOOPS                |             |     2 |    84 |     4   (0)| 00:00:01 |

|*  3 |    TABLE ACCESS BY INDEX ROWID| EMP         |     2 |    58 |     2   (0)| 00:00:01 |

|*  4 |     INDEX RANGE SCAN          | EMP_SAL_IDX |     8 |       |     1   (0)| 00:00:01 |

|*  5 |    INDEX UNIQUE SCAN          | PK_DEPT     |     1 |       |     0   (0)| 00:00:01 |

|   6 |   TABLE ACCESS BY INDEX ROWID | DEPT        |     1 |    13 |     1   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):

---------------------------------------------------

   3 - filter("JOB"='SALESMAN')

   4 - access("SAL">=1500)

   5 - access("D"."DEPTNO"="DEPTNO")


<일반 조인문>

SQL> select e.empno, e.ename, e.job, e.mgr, e.sal, d.dname

  2  from   emp e, dept d

  3  where  d.deptno = e.deptno

  4  and    e.job = 'SALESMAN'

  5  and    e.sal >= 1500;


Execution Plan

---------------------------------------------------------------------------------------------

| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |             |     2 |    84 |     4   (0)| 00:00:01 |

|   1 |  NESTED LOOPS                 |             |       |       |            |          |

|   2 |   NESTED LOOPS                |             |     2 |    84 |     4   (0)| 00:00:01 |

|*  3 |    TABLE ACCESS BY INDEX ROWID| EMP         |     2 |    58 |     2   (0)| 00:00:01 |

|*  4 |     INDEX RANGE SCAN          | EMP_SAL_IDX |     8 |       |     1   (0)| 00:00:01 |

|*  5 |    INDEX UNIQUE SCAN          | PK_DEPT     |     1 |       |     0   (0)| 00:00:01 |

|   6 |   TABLE ACCESS BY INDEX ROWID | DEPT        |     1 |    13 |     1   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):

---------------------------------------------------

   3 - filter("E"."JOB"='SALESMAN')

   4 - access("E"."SAL">=1500)

   5 - access("D"."DEPTNO"="E"."DEPTNO")


☞ 단순 뷰를 Merging 할 경우, 파라미터 or 힌트 설정을 하지 않을 경우 일반 조인문과 똑같은 형태로 변환 후 처리




(3) 복합 뷰(Complex View) Merging

  • group by절
  • select-list에 distinct연산자 포함하는 복합 뷰

※ _complex_view_merging 파라미터 값이 true로 설정할 때만 Merging 발생


10g에서는 복합 뷰 Merging을 일단 시도하지만, 원본 쿼리에 대해서도 비용을 같이 계산해 Merging했을 때의 비용이 더 낮을 때만 그것을 채택 (비용기반 쿼리 변환)


10g 이전 _complex_view_merging 파라미터 기본 값  (8i : false, 9i : true)

  • complex_view_merging 파라미터를 true로 설정해도 Merging 될 수 없는 복합 뷰
    • 집합(set)연산자( union, union all, intersect, minus )
    • connect by절
    • ROWNUM pseudo 컬럼
    • select-list에 집계 함수(avg, count, max, min, sum)사용 : group by 없이 전체를 집계하는 경우를 말함
    • 분석 함수


  • 복합뷰를 포함한 쿼리 (뷰 머징 발생 시)

SQL> select d.dname, avg_sal_dept

  2  from   dept d

  3        ,(select deptno, avg(sal) avg_sal_dept from emp group by deptno) e

  4  where  d.deptno = e.deptno

  5  and    d.loc='CHICAGO';


DNAME          AVG_SAL_DEPT

-------------- ------------

SALES            1566.66667


Execution Plan

------------------------------------------------------------------------------------------------

| 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                |                |       |       |            |          |

|   3 |    NESTED LOOPS               |                |     5 |   135 |     4   (0)| 00:00:01 |

|*  4 |     TABLE ACCESS FULL         | DEPT           |     1 |    20 |     3   (0)| 00:00:01 |

|*  5 |     INDEX RANGE SCAN          | EMP_IDX_DEPTNO |     5 |       |     0   (0)| 00:00:01 |

|   6 |    TABLE ACCESS BY INDEX ROWID| EMP            |     5 |    35 |     1   (0)| 00:00:01 |

------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):

---------------------------------------------------

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

   5 - access("D"."DEPTNO"="DEPTNO")


  • 복합뷰를 일반 조인절로 변경한 쿼리

SQL> select d.dname,avg(sal)

  2  from   dept d,emp e

  3  where  d.deptno=e.deptno

  4  and    d.loc='CHICAGO'

  5  group by d.rowid,d.dname;


DNAME            AVG(SAL)

-------------- ----------

SALES          1566.66667


Execution Plan

------------------------------------------------------------------------------------------------

| Id  | Operation                     | Name           | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |                |     1 |    27 |     5  (20)| 00:00:01 |

|   1 |  HASH GROUP BY                |                |     1 |    27 |     5  (20)| 00:00:01 |

|   2 |   NESTED LOOPS                |                |       |       |            |          |

|   3 |    NESTED LOOPS               |                |     5 |   135 |     4   (0)| 00:00:01 |

|*  4 |     TABLE ACCESS FULL         | DEPT           |     1 |    20 |     3   (0)| 00:00:01 |

|*  5 |     INDEX RANGE SCAN          | EMP_IDX_DEPTNO |     5 |       |     0   (0)| 00:00:01 |

|   6 |    TABLE ACCESS BY INDEX ROWID| EMP            |     5 |    35 |     1   (0)| 00:00:01 |

------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):

---------------------------------------------------

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

   5 - access("D"."DEPTNO"="E"."DEPTNO")


  • 뷰머징이 발생 할 경우
    • dept테이블에서 loc컬럼이 'CHICAGO'인 데이터를 먼저 필터링하고 조인, 조인대상 집합만 group by 실행
  • 뷰머징이 발생되지 않을 경우 
    • emp 테이블의 모든 테이블을 group by 한 후 필터링하게 되면서 불필요한 레코드 엑세스 발생



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

  • 9i : 복합 뷰를 무조건 머징 => 대부분 더 나은 성능 제공하지만 복합뷰 머징 시 그렇지 못할 때가 많음

  ☞ no_merge 힌트 등 뷰안에 rownum 을 넣어주는 튜닝 기법 활용


  • 10g 이후 비용기반 쿼리 변환 방식으로 처리

  ☞ _optimizer_cost_based_transformation 파라미터 사용 → 설정값 5가지 (on, off, exhaustive, linear, iteraive)

    • on   :  적절한 것을 스스로 선택 
    • exhaustive :  cost가 가장 저렴한 것 선택
    • linear   :  순차적 비교 후 선택
    • literation :  변환이 수행 유무에 따른 cost를 비교하기 위한 경우의 수로 listeration 정의


  • opt_param 힌트 이용으로 쿼리 레벨에서 파라미터 변경가능 (10gR2부터 제공)  p.496 참조


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

  • 1단계 : 뷰머징 시행 시 오히려 비용이 증가된다고 판단(10g이후) 되거나, 부정확한 결과 집합 가능성이 있을 시 뷰머징 포기
  • 2단계 : 뷰머징이 포기 할 경우 조건절 Pushing 시도 (다음절에서 자세히 다룸)
  • 3단계 : 뷰 쿼리 블록을 개별적으로 최적화된 개별 플랜을 전체 실행계획에 반영 (즉, 뷰 쿼리 수행 결과를 엑세스 쿼리에 전달)

SQL> select /*+ leading(e) use_nl(d) */ *

  2  from   dept d

  3       ,(select /*+ NO_MERGE */ * from emp) e

  4  where  e.deptno = d.deptno;


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


Execution Plan

----------------------------------------------------------------------------------------

| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |         |    14 |  1498 |    17   (0)| 00:00:01 |

|   1 |  NESTED LOOPS                |         |       |       |            |          |

|   2 |   NESTED LOOPS               |         |    14 |  1498 |    17   (0)| 00:00:01 |

|   3 |    VIEW                      |         |    14 |  1218 |     3   (0)| 00:00:01 |

|   4 |     TABLE ACCESS FULL        | EMP     |    14 |   532 |     3   (0)| 00:00:01 |

|*  5 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |

|   6 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |

----------------------------------------------------------------------------------------


Predicate Information (identified by operation id):

---------------------------------------------------

   5 - access("E"."DEPTNO"="D"."DEPTNO")


     ※ 실행계획의 "VIEW" 로 표시된 오퍼레이션 단계가 추가 되었을 시, 실제로 다음 단계로 넘어가기 전 중간집합을 생성하는 것은 아님



  • 오라클 고도화 원리와 해법 2 (bysql.net 2011년 1차 스터디)
  • 작성자: 위충환 (실천하자)
  • 최초작성일: 2011년 05월 16 일
  • 본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net
  • 문서의 잘못된 점이나 질문사항은 본 문서에 댓글로 남겨주세요. ^^


번호 제목 글쓴이 날짜 조회 수
40 8. 고급 조인 테크닉-2 file suspace 2011.04.05 7009
39 2. 옵티마이저 행동에 영향을 미치는 요소 휘휘 2011.04.18 5086
38 1. 옵티마이저 file 실천하자 2011.04.18 11203
37 3. 옵티마이저의 한계 멋진넘 2011.04.19 7852
36 6. 히스토그램 실천하자 2011.04.25 10910
35 4. 통계정보 Ⅰ darkbeom 2011.04.26 18083
34 5. 카디널리티 suspace 2011.04.26 5950
33 8. 통계정보 Ⅱ [1] 멋진넘 2011.04.30 31050
32 1. 쿼리 변환이란? 실천하자 2011.05.02 4922
31 7. 비용 휘휘 2011.05.03 6160
» 3. 뷰 Merging 실천하자 2011.05.15 23376
29 2. 서브쿼리 Unnesting darkbeom 2011.05.16 19691
28 5. 조건절 이행 휘휘 2011.05.30 5404
27 6. 조인 제거 멋진넘 2011.05.30 4588
26 7. OR-Expansion 멋진넘 2011.05.31 8714
25 4. 조건절 Pushing 실천하자 2011.05.31 7014
24 12. 기타 쿼리 변환 [3] 실천하자 2011.06.03 6641
23 8. 공통 표현식 제거 darkbeom 2011.06.07 5213
22 9. Outer 조인을 Inner 조인으로 변환 darkbeom 2011.06.07 7829
21 10. 실체화 뷰 쿼리로 재작성 suspace 2011.06.07 5526