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)