3. 뷰 Merging
2011.05.17 19:48
03 뷰 Merging
(1)뷰 Merging란?
<쿼리1> <쿼리2>
select * |
select * (select * from emp where job='SALESMAN') a, |
===>
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 |
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 |
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 되지 않은 뷰의 처리방식
- . 뷰merging 시 cost가 증가되면(10g) , 부정확한 결과집합이 만들어질 가능성이 있을 경우 옵티마이져는 뷰merging을 포기
- 2차적으로 조건절 pusing을 시도
- 뷰쿼리 블록을 개별적으로 최적화하고 생성된 서브플랜을 전체 실행계획을 생성하는데 사용
<뷰 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)
댓글 0
번호 | 제목 | 글쓴이 | 날짜 | 조회 수 |
---|---|---|---|---|
55 | Front Page | 운영자 | 2011.02.16 | 114638 |
54 | 1. 테이블 파티셔닝 | 오라클잭 | 2011.06.21 | 28304 |
53 | 5. 병렬 처리에 관한 기타 상식 [1] | balto | 2011.06.26 | 28181 |
52 | 1. Nested Loops 조인 | 오라클잭 | 2011.03.23 | 23045 |
51 | 6. 스칼라 서브쿼리를 이용한 조인 | balto | 2011.03.27 | 18972 |
50 | 2. 인덱스 기본 원리 | balto | 2011.02.18 | 18858 |
49 | 5. Outer 조인 | 휘휘 | 2011.03.28 | 17674 |
48 | 4. 조건절 Pushing | 오예스 | 2011.05.31 | 17566 |
47 | 6. 히스토그램 | 오예스 | 2011.04.25 | 17498 |
46 | 4. 통계정보 Ⅰ | 토시리 | 2011.04.25 | 16040 |
45 | 1. 인덱스 구조 | 운영자 | 2011.03.30 | 15969 |
44 | 4. PQ_DISTRIBUTE 힌트 | 휘휘 | 2011.06.27 | 15709 |
43 | 6. IOT, 클러스터 테이블 활용 | 오예스 | 2011.02.26 | 15696 |
42 | 5. 카디널리티 | 오라클잭 | 2011.04.27 | 12939 |
41 | 8. 고급 조인 테크닉-2 | 오라클잭 | 2011.04.05 | 12587 |
40 | 2. 파티션 Pruning | 휘휘 | 2011.06.19 | 11920 |
39 | 2. 병렬 Order By와 Group By | 휘휘 | 2011.06.30 | 10190 |
38 | 7. 조인을 내포한 DML 튜닝 | 오예스 | 2011.04.04 | 9970 |
37 | 10. 실체화 뷰 쿼리로 재작성 | 오라클잭 | 2011.06.08 | 9962 |
36 | 5. 테이블 Random 액세스 최소화 튜닝 | 휘휘 | 2011.02.26 | 9516 |