Chap04. 03 뷰 Merging

Tech_Wave 2021. 8. 13. 17:07

01. 뷰 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 와 같은 형태로 변환 되는데, 이를 "뷰 Merging" 이라고 합니다.
이처럼 뷰 Merging을 거친 쿼리는 옵티마이저가 더 다양한 액세스 경로를 조사 대상으로 삼을 수 있게 됩니다.
관련 힌트로는 Merge, No_Merge가 있습니다.

[ MERGE ] : 뷰 Merging을 발생

[ NO_MERGE ] : 뷰 Merging을 방지

02. 단순 뷰(Simple View) Merging

조건절과 조인문만을 포함하는 단순 뷰는 No_Merge 힌트를 사용하지 않는 한 언제든 Merging이 일어납니다.
반면, Group by 절이나 Distinct 연산을 포함하는 복합 뷰는 파라미터 설정 또는 Hint 사용에 의해서만 뷰 Merging이 가능합니다.
집합연산자, Connect by, rownum 등을 포함하는 복합 뷰는 아예 뷰 Merging이 불가능합니다.

-- View Merging 예시
CREATE OR REPLACE VIEW EMP_SALESMAN
AS
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
WHERE JOB = 'SALESMAN';


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

위 쿼리를 뷰 Merging 하지 않고 그대로 최적화한다면 아래와 같은 실행계획이 만들어 집니다.

뷰 Merging이 작동한다면 변환된 Query는 아래와 같은 모습일 것입니다.

SELECT E.EMPNO, E.ENAME, E.JOB, E.MGR, E.SAL, D.DNAME
FROM EMP E, DEPT D
WHERE D.DEPTNO = E.DEPTNO
AND E.JOB = 'SALESMAN'
AND E.SAL >= 1500;

03. 복합 뷰(Complex View) Merging

아래 항목을 포함하는 복합 뷰는 _complex_view_merging 파라미터를 true로 설정할 때만 Merging이 일어난다.
1. Group by 절
2. SELECT-List 에 Distinct 연산자 포함
_complex_view_merging 파라미터를 True로 설정하더라도 아래 항목들을 포함하는 복합 뷰는 Merging 될 수 없습니다.
- 집합(set)연산자(union, union all, intersect, minus)
- connect by 절
- ROWNUM pseudo 컬럼
- select-list에 집계 함수(avg, count, max, min, sum) 사용 : group by 없이 전체를 집계하는 경우를 말함
- 분석 함수(Analytic Function)

아래는 복합 뷰를 포함한 쿼리 예시 입니다.

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

뷰 쿼리 블록을 액세스 쿼리 블록과 Merging 하고 나면 아래와 같은 형태가 됩니다.

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;

뷰 Merging이 일어난다면 두 쿼리는 똑같이 아래 실행계획을 사용한다.

위 쿼리가 뷰 Merging을 통해 얻을 수 있는 이점은, D.LOC = 'CHICAGO' 인 데이터만 선택해서 조인하고, 조인에 성공한 집합만 Group by 한다는 데 있습니다.
만약 뷰를 Merging하지 않는다면 EMP 테이블에 있는 모든 데이터를 Group by 해서 조인하고 나서야 LOC = 'CHICAGO' 조건을 필터링하게 되므로 EMP 테이블을 스캔하는 과정에서 불필요한 레코드 액세스가 많이 발생하게 됩니다.

04. 비용기반 쿼리 변환의 필요성

10g부터는 비용기반 쿼리 변환 방식으로 전환하게 되었고, 이 기능을 제어하기 위한 파라미터가 "_OPTIMIZER_COST_BASED_TRANSFORMATION" 입니다.
- 설정 값 유형 : ON, OFF, EXHAUSTIVE, LINEAR, ITERATIVE
비용기반 쿼리 변환이 휴리스틱 쿼리 변환보다 고급 기능이긴 하지만, 파싱 과정에서 더 많은 일을 수행해야만 한다.
약간의 하드 파싱 부하를 감수하더라도 더 나은 실행계획을 얻으려는 것이므로 이들 파라미터를 off 시키는 것은 바람직하지 않습니다.
각 쿼리 변환마다 제어할 수 있는 힌트가 따로 있고, 필요하다면 opt_param 힌트를 이용해 아래와 같이 쿼리 레벨에서 파라미터를 변경할 수 있습니다.

SELECT /*+ OPT_PARAM('_OPTIMIZER_PUSH_PRED_COST_BASED', 'FALSE') */ FROM .....

05. Merging 되지 않은 뷰의 처리방식

뷰 Merging을 시행했을 때 오히려 비용이 더 증가한다고 판단되거나 부정확한 결과집합이 만들어질 가능성이 있을 때 옵티마이저는 뷰 Merging을 포기합니다.
어떤 이유에서건 뷰 Merging이 이루어지지 않았을 땐 2차적으로 조건절 Pushing을 시도합니다.
- 하지만 이마저도 실패한다면 뷰 쿼리 블록을 개별적으로 최적화하고, 거기서 생성된 서브플랜을 전체 실행계획을 생성하는데 사용합니다.

아래는 단순 뷰를 참조하므로 버전에 상관없이 항상 뷰 Merging이 일어난다.

SELECT /*+ LEADING(E) USE_NL(D) */ *
FROM DEPT D, (SELECT * FROM EMP) E
WHERE E.DEPTNO = D.DEPTNO;

아래는 no_merge 힌트를 사용해 뷰 Merging을 방지했을 때의 실행계획입니다.

SELECT /*+ LEADING(E) USE_NL(D) */ *
FROM DEPT D, (SELECT /*+ NO_MERGE */ * FROM EMP) E
WHERE E.DEPTNO = D.DEPTNO;

여기서 오해하지 말 것은, 실행계획에 "VIEW"라고 표시된 오퍼레이션 단계가 추가되었다고 해서 다음 단계로 넘어가기 전에 중간집합을 생성하는 것은 아니라는 점입니다.
따라서 실행계획이 위와 같더라도 완전한 부분범위 처리가 가능합니다.

아래처럼 뷰가 NL 조인에서 Inner 테이블로서 액세스될 때는 어떻게 처리될 것으로 예상될까요?

SELECT /*+ LEADING(D) USE_NL(E) */ *
FROM DEPT D, (SELECT /*+ NO_MERGE */ * FROM EMP) E
WHERE E.DEPTNO = D.DEPTNO;

이때도 VIEW 처리 단계에서 중간집합을 생성하지는 않습니다.
따라서 드라이빙 테이블 DEPT에서 읽은 건수만큼 EMP 테이블에 대한 Table Full Scan을 반복합니다.

아래처럼 인라인 뷰에 ORDER BY 절을 추가하고 다시 수행해보자.

SELECT /*+ LEADING(D) USE_NL(E) */ *
FROM DEPT D, (SELECT /*+ NO_MERGE */ * FROM EMP ORDER BY ENAME) E
WHERE E.DEPTNO = D.DEPTNO;

실제 테스트한 내용과 책에서의 결과 내용과 다르긴 하지만,,, 책은
emp 테이블의 액세스가 줄어든 점을 확인할 수 있었고, emp 테이블은 한 번만 Full Scan했고, 소트 수행 후 PGA에 저장된 중간집합을 반복 액세스한 것을 알 수 있어, 따라서 추가적인 블록 I/O가 발생하지 않았다는 점입니다.