[Oracle] 오라클 쿼리 변환 - 뷰 Merging

☑ 쿼리 변환이란?

뷰 Merging

SELECT *

  FROM (SELECT * FROM EMP WHERE JOB = 'SALESMAN') A,

(SELECT * FROM DEPT WHERE LOC = 'CHICAGO') B

WHERE A.DEPTNO = B.DEPTNO;

▼

SELECT *

  FROM EMP A, DEPT B

WHERE A.DEPTNO = B.DEPTNO

    AND A.JOB = 'SALESMAN'

    AND B.LOC = 'CHICAGO';

- 뷰 Merging을 통해 옵티마이저는 더 다양한 access 경로를 조사 대상으로 삼게 됨

단순 뷰(Simple View) Merging

- 단순 뷰 : 조건절과 JOIN문만을 포함, no_merge 힌트를 사용하지 않는 한 언제든 Merging 발생

-- 조건절 하나만을 가진 단순 뷰

CREATE OR REPLACE VIEW EMP_SALESMAN AS

SELECT EMPNO, EMANE, 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;

▼

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;

복합 뷰(Complex View) Merging

- _complex_view_merging 파라미터를 true로 설정할 때만 Merging 발생

① group by 절

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

▼

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을 함으로써 DEPT.LOC = 'CHICAGO'인 데이터만 선택해서 JOIN하고, JOIN에 성공한 집합만 GROUP BY

* 만약 DEPT.LOC = 'CHICAGO' 조건에 의해 선택된 DEPTNO가 EMP테이블에서 많은 비중을 차지한다면 오히려 Table Full Scan을 감수하더라도 group by로 먼저 집합을 줄이고 나서 JOIN하는 편이 더 나음 

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