4. 뷰 Merging

pine tree root 2022. 1. 23. 13:11

4. 뷰 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 불가능

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

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 될 수 없는 복합 뷰

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

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

뷰머징이 발생 할 경우

뷰머징이 발생되지 않을 경우

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

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

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

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

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

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" 로 표시된 오퍼레이션 단계가 추가 되었을 시, 실제로 다음 단계로 넘어가기 전 중간집합을 생성하는 것은 아님

참고

※ view_merging 힌트를 쓰지 않고도 view_merging을 방지하고 싶다면 복합쿼리뷰나 단순쿼리뷰에 rownum을 포함시켜주어야 한다.

그 이유는 rownum을 이용해서 필터링을 하거나 하는 다양한 변수가 발생할수 있기 때문에 아예 view merging에 대한 시도를 optimizer에서 포기한다.