메뉴 건너뛰기

bysql.net

3. 뷰 Merging

2011.05.17 19:48

오라클잭 조회 수:6080

03 뷰 Merging

 

(1)뷰 Merging란?

<쿼리1>                                                                                                                        <쿼리2>

 

select *
from emp a,dept b
where a.deptno=b.deptno
and a.job='SALESMAN'
and b.loc='CHICAGO'

select *
from

     (select * from emp where job='SALESMAN') a,
     (select * from dept where loc='CHICAGO') b
where a.deptno=b.deptno; 
                

           

           ===>

 

 

 

 

 

1. <쿼리1>의 뷰 쿼리 블록은 액세스 쿼리 블록과의 Merge 과정을 거쳐 <쿼리2>와 같은 형태로 변환된다

2. 제어힌트: merge , no_merge

3. 뷰 Merge를 통해 같은 실행 계획으로 수행되어 쿼리 성능에 차이가 없다

   but 조건절 pushing이 불가능한 뷰를 사용했을 때 성능이 느려지기도 한다.

 

 

(2)단순 뷰(Simple View) Merging

  • 조건절과 조인문만 포함하는 단순 뷰는 no_merge를 하용하지 않는한 언제든지 Merging이 일어난다
  • groupby,distinct 연산을 포함하는 복합뷰는 힌트에 의해서만 뷰 merging이 일어난다
  • 집합(set)연산자, connect by, rownum 등을 포함하는 복합뷰는 뷰 Merging이 불가능

 

 ----------------------------------------------------------------------------------------------------------------------------------------------- <TEST>

-조건절 하나만을 가진 단순 view 생성

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;

 Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
      2   NESTED LOOPS  (cr=8 pr=0 pw=0 time=95 us)
      2    VIEW  EMP_SALESMAN (cr=4 pr=0 pw=0 time=77 us)
      2     TABLE ACCESS BY INDEX ROWID EMP (cr=4 pr=0 pw=0 time=64 us)
      8      INDEX RANGE SCAN EMP_SAL_IDX (cr=2 pr=0 pw=0 time=105 us)(Object ID 45661)
      2    TABLE ACCESS BY INDEX ROWID DEPT (cr=4 pr=0 pw=0 time=63 us)
      2     INDEX UNIQUE SCAN DEPT_PK (cr=2 pr=0 pw=0 time=30 us)(Object ID 45660)

 

       *뷰가 merge 되지 않음         

               

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;

               

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
      2   NESTED LOOPS  (cr=8 pr=0 pw=0 time=67 us)
      2    TABLE ACCESS BY INDEX ROWID EMP (cr=4 pr=0 pw=0 time=53 us)
      8     INDEX RANGE SCAN EMP_SAL_IDX (cr=2 pr=0 pw=0 time=101 us)(Object ID 45661)
      2    TABLE ACCESS BY INDEX ROWID DEPT (cr=4 pr=0 pw=0 time=53 us)
      2     INDEX UNIQUE SCAN DEPT_PK (cr=2 pr=0 pw=0 time=25 us)(Object ID 45660)

 

      *뷰가 merge되어 일반? 조인문을 처리하는 것과 같다

??

(3)복합 뷰(Complex View) Merging

 

-아래 항목을 포함하는 복합뷰(complex view)는 _complex_view_merging=true 일때만 mersing 이 일어난다

  • group by 절
  • select-list에 distinct 연산자 포함

-8i       _complex_view_merging 기본값이 false

-9i      동일한 결과가 보장하는한 복합 view merging이 항상 일어난다

-10g    원본쿼리에 대해 비용을 계산해서 비교 선택

 

*_complex_view_merging=true 이더라도 다음 항목을 포함하는 복합 view 는 merging할 수 없다*

 

  • 집합연산자(union,union all,intersect,minus)
  • connect by 절
  • rownum pseudo 절
  • select-list에 집계함수(avg,count,max,min,sum) 사용:group by 없이 전체를 집계하는 경우를 뜻함
  • 분석함수

 

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

 

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
      1   NESTED LOOPS  (cr=12 pr=0 pw=0 time=452 us)
      3    VIEW  (cr=7 pr=0 pw=0 time=435 us)
      3     HASH GROUP BY (cr=7 pr=0 pw=0 time=419 us)
     15      TABLE ACCESS FULL EMP (cr=7 pr=0 pw=0 time=84 us)
      1    TABLE ACCESS BY INDEX ROWID DEPT (cr=5 pr=0 pw=0 time=82 us)
      3     INDEX UNIQUE SCAN DEPT_PK (cr=2 pr=0 pw=0 time=42 us)(Object ID 45660)

 

*뷰 merging이 안 일어나면 emp 전체를 group by 해서 조인하고 나서 loc='CHICAGO'로 필터링되는

 불필요한 레코드 액세스 발생

 

 

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


Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
      1   HASH GROUP BY (cr=24 pr=0 pw=0 time=840 us)
      6    NESTED LOOPS  (cr=24 pr=0 pw=0 time=354 us)
     15     TABLE ACCESS FULL EMP (cr=7 pr=0 pw=0 time=98 us)
      6     TABLE ACCESS BY INDEX ROWID DEPT (cr=17 pr=0 pw=0 time=476 us)
     15      INDEX UNIQUE SCAN DEPT_PK (cr=2 pr=0 pw=0 time=230 us)(Object ID 45660)

 

*뷰 Merging이 일어나 loc='CHICAGO'인 데이터에 조인이 성공한 집합만 group by하는 이점이 있다

 

 

 

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

 

-9i  무조건 뷰 merging 하도록 구현.보편적은 더 나은 성능

       but 복합뷰는 그렇지 못할 때가 많다

 

-10g 비용기반 쿼리변환 방식으로 전환(제어 _optimizer_cost_based_transformation)

        비용계산후 비교 후에 뷰merging을 쓸지 원본쿼리를 쓸지 결정

 

 

<The logical optimizer 226 page에 자세히 설명 ㅋ>

      _optimizer_cost_based_transformation       

  •     on  :설정 할 수 있는 방법 중 적절한 것을 스스로 선택 
  • exhaustive  :모든 경우의 수를 고려하여 cost가 가장 저렴한 것을 선택
  • linear  :순차적으로 비교한 후 선택
  • literation :변환이 수행된 경우와 그렇지 않은 경우의 cost를 비교하기 위한 경우의 수로 listeration 정의

 

 <5>Merging 되지 않은 뷰의 처리방식

  1. . 뷰merging 시 cost가 증가되면(10g) , 부정확한 결과집합이 만들어질 가능성이 있을 경우 옵티마이져는 뷰merging을 포기
  2.   2차적으로 조건절 pusing을 시도
  3.  뷰쿼리 블록을 개별적으로 최적화하고 생성된 서브플랜을 전체 실행계획을 생성하는데 사용

 

<뷰 merging이 일어남>

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

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
     15   NESTED LOOPS  (cr=27 pr=0 pw=0 time=84 us)
     15    TABLE ACCESS FULL EMP (cr=9 pr=0 pw=0 time=127 us)
     15    TABLE ACCESS BY INDEX ROWID DEPT (cr=18 pr=0 pw=0 time=310 us)
     15     INDEX UNIQUE SCAN DEPT_PK (cr=3 pr=0 pw=0 time=132 us)(Object ID 45660)

 

<뷰merging 방지>

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

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
     15   NESTED LOOPS  (cr=27 pr=0 pw=0 time=106 us)
     15    VIEW  (cr=9 pr=0 pw=0 time=209 us)
     15     TABLE ACCESS FULL EMP (cr=9 pr=0 pw=0 time=143 us)
     15    TABLE ACCESS BY INDEX ROWID DEPT (cr=18 pr=0 pw=0 time=307 us)
     15     INDEX UNIQUE SCAN DEPT_PK (cr=3 pr=0 pw=0 time=130 us)(Object ID 45660)

 

 

 

 

 

번호 제목 글쓴이 날짜 조회 수
35 1. 옵티마이저 file 휘휘 2011.04.18 6057
34 3. 옵티마이저의 한계 - P 휘휘 2011.04.18 3695
33 2. 옵티마이저 행동에 영향을 미치는 요소 balto 2011.04.18 6158
32 3. 옵티마이저의 한계 휘휘 2011.04.19 6698
31 4. 통계정보 Ⅰ file 토시리 2011.04.25 16002
30 6. 히스토그램 오예스 2011.04.25 17363
29 5. 카디널리티 오라클잭 2011.04.27 12913
28 7. 비용 file balto 2011.05.02 4989
27 8. 통계정보 Ⅱ AskZZang 2011.05.04 5913
26 1. 쿼리 변환이란? 운영자 2011.05.16 6254
» 3. 뷰 Merging 오라클잭 2011.05.17 6080
24 2. 서브쿼리 Unnesting 토시리 2011.05.18 2074
23 5. 조건절 이행 file balto 2011.05.30 5460
22 4. 조건절 Pushing 오예스 2011.05.31 17467
21 6. 조인 제거 AskZZang 2011.06.01 5440
20 7. OR-Expansion AskZZang 2011.06.01 8320
19 12. 기타 쿼리 변환 휘휘 2011.06.06 3118
18 10. 실체화 뷰 쿼리로 재작성 오라클잭 2011.06.08 9949
17 11. 집합 연산을 조인으로 변환 오라클잭 2011.06.08 4954
16 2. 소트를 발생시키는 오퍼레이션 file balto 2011.06.12 4846