뷰머징
<쿼리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>와 같은 형태로 변환되는데 이를 뷰머징이라고 한다.
이와 같이 쿼리가 풀어지면 옵티마이저는 더욱 다양한 액세스 경로를 탐색할 수 있게 된다.
예시
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 ;
--------------------------------------------------------------
| Id | Operation | Name | E-Rows |
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | NESTED LOOPS | | |
| 2 | NESTED LOOPS | | 1 |
| 3 | VIEW | EMP_SALESMAN | 1 | -> 뷰 그대로 사용
|* 4 | TABLE ACCESS FULL | EMP | 1 |
|* 5 | INDEX UNIQUE SCAN | DEPT_PK | 1 |
| 6 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 |
--------------------------------------------------------------
뷰머징을 해준다면..
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 ;
---------------------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | NESTED LOOPS | | |
| 2 | NESTED LOOPS | | 1 |
|* 3 | TABLE ACCESS FULL | EMP | 1 |
|* 4 | INDEX UNIQUE SCAN | DEPT_PK | 1 |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 |
---------------------------------------------------------
예시
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'
-----------------------------
| Id | Operation |
-----------------------------
| 0 | SELECT STATEMENT |
| 1 | HASH GROUP BY |
|* 2 | HASH JOIN |
|* 3 | TABLE ACCESS FULL|
| 4 | TABLE ACCESS FULL|
-----------------------------
-> 뷰를 해체하여 일반 조인으로 변경하였다.
select d.dname, avg_sal_dept
from dept d
, (select /*+no_merge*/deptno, avg(sal) avg_sal_dept
from emp
group by deptno) e
where d.deptno = e.deptno
and d.loc = 'CHICAGO'
-------------------------------
| Id | Operation |
-------------------------------
| 0 | SELECT STATEMENT |
|* 1 | HASH JOIN |
| 2 | JOIN FILTER CREATE |
|* 3 | TABLE ACCESS FULL |
| 4 | VIEW |
| 5 | HASH GROUP BY |
| 6 | JOIN FILTER USE |
|* 7 | TABLE ACCESS FULL|
-------------------------------
테스트 : 뷰머징 불가능 케이스
--집합연산의 경우
select d.dname
from dept d
, (select *
from emp
where job = 'MANAGER'
union all
select *
from emp
where job = 'SALESMAN') e
where d.deptno = e.deptno
and d.loc = 'CHICAGO'
------------------------------
| Id | Operation |
------------------------------
| 0 | SELECT STATEMENT |
|* 1 | HASH JOIN |
|* 2 | TABLE ACCESS FULL |
| 3 | VIEW |
| 4 | UNION-ALL |
|* 5 | TABLE ACCESS FULL|
|* 6 | TABLE ACCESS FULL|
------------------------------
merge가 되지 않는다.
--connect by 사용한 경우
select d.dname
from dept d
, (select
from emp
start with mgr is null
connect by nocycle prior empno = mgr
) e
where d.deptno = e.deptno
and d.loc = 'CHICAGO'
---------------------------------------------------
| Id | Operation |
---------------------------------------------------
| 0 | SELECT STATEMENT |
|* 1 | HASH JOIN |
|* 2 | TABLE ACCESS FULL |
| 3 | VIEW |
|* 4 | CONNECT BY NO FILTERING WITH START-WITH|
| 5 | TABLE ACCESS FULL |
---------------------------------------------------
-->merge 안 됨
select d.dname
from dept d
, (select /*+merge*/*
from emp
start with mgr is null
connect by nocycle prior empno = mgr
) e
where d.deptno = e.deptno
and d.loc = 'CHICAGO'
---------------------------------------------------
| Id | Operation |
---------------------------------------------------
| 0 | SELECT STATEMENT |
|* 1 | HASH JOIN |
|* 2 | TABLE ACCESS FULL |
| 3 | VIEW |
|* 4 | CONNECT BY NO FILTERING WITH START-WITH|
| 5 | TABLE ACCESS FULL |
---------------------------------------------------
-->hint를 사용해도 안 됨
--rownum을 사용한 경우
select d.dname, e.empno
from dept d
, (select /*+merge*/rownum, deptno, empno
from emp) e
where d.deptno = e.deptno
and d.loc = 'CHICAGO'
-------------------------------------
| Id | Operation | Name |
-------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | HASH JOIN | |
|* 2 | TABLE ACCESS FULL | DEPT |
| 3 | VIEW | |
| 4 | COUNT | |
| 5 | TABLE ACCESS FULL| EMP |
-------------------------------------
-->역시 안 됨
--전체 집계함수를 사용한 경우
select d.dname, avg_sal_dept
from dept d
, (select /*+merge*/ max(deptno) deptno, avg(sal) avg_sal_dept
from emp) e
where d.deptno = e.deptno
and d.loc = 'CHICAGO'
--------------------------------------
| Id | Operation |
--------------------------------------
| 0 | SELECT STATEMENT |
| 1 | NESTED LOOPS |
| 2 | VIEW |
| 3 | SORT AGGREGATE |
| 4 | TABLE ACCESS FULL |
|* 5 | TABLE ACCESS BY INDEX ROWID|
|* 6 | INDEX UNIQUE SCAN |
--------------------------------------
--집계함수 사용한 경우
select d.dname, avg_sal_dept
from dept d
, (select /*+merge*/deptno, avg(sal) over(partition by empno) avg_sal_dept
from emp) e
where d.deptno = e.deptno
and d.loc = 'CHICAGO'
------------------------------
| Id | Operation |
------------------------------
| 0 | SELECT STATEMENT |
|* 1 | HASH JOIN |
|* 2 | TABLE ACCESS FULL |
| 3 | VIEW |
| 4 | WINDOW SORT |
| 5 | TABLE ACCESS FULL|
------------------------------
예시
-- 단순뷰였을 때 Merging 실행계획
select /*+ leading(e) use_nl(d) */ *
from dept d
, (select * from emp) e
where e.deptno = d.deptno
--------------------------------------------
| Id | Operation | Name | E-Rows |
--------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | NESTED LOOPS | | 14 |
| 2 | TABLE ACCESS FULL| EMP | 14 |
|* 3 | TABLE ACCESS FULL| DEPT | 1 |
--------------------------------------------
-- no_merge문을 사용했을 때 실행계획
select /*+ leading(e) use_nl(d) */ *
from dept d
, (select /*+no_merge*/* from emp) e
where e.deptno = d.deptno
---------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | NESTED LOOPS | | 14 |
| 2 | VIEW | | 14 |
| 3 | TABLE ACCESS FULL| EMP | 14 |
|* 4 | TABLE ACCESS FULL | DEPT | 1 |
---------------------------------------------
'VIEW'라고 표시되었다고 해서 중간집합을 만드는 것은 아니다!
위와 같은 실행계획에서도 부분범위 처리가 가능하다!
select /*+ leading(e) use_nl(d) */ *
from dept d
, (select /*+no_merge*/* from emp) e
where e.deptno = d.deptno
and rownum = 1
----------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |
|* 1 | COUNT STOPKEY | | 1 | | 1 |
| 2 | NESTED LOOPS | | 1 | | 1 |
| 3 | NESTED LOOPS | | 1 | 1 | 1 |
| 4 | VIEW | | 1 | 1 | 1 |
| 5 | TABLE ACCESS FULL | EMP | 1 | 14 | 1 |
|* 6 | INDEX UNIQUE SCAN | DEPT_PK | 1 | 1 | 1 |-->부분범위 처리하고 있음
| 7 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 1 | 1 |
----------------------------------------------------------------------------
view라고 표시된 오퍼레이션 단계가 추가되었다고 해서 다음 단계로 넘어가기 전에 중간집합을 생성하는 것은 아님을 알 수 있다.
select /*+ leading(d) use_nl(e) */ *
from dept d
, (select /*+no_merge*/* from emp) e
where e.deptno = d.deptno
--------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 14 |00:00:00.01 | 12 |
| 1 | NESTED LOOPS | | 1 | 14 | 14 |00:00:00.01 | 12 |
| 2 | TABLE ACCESS FULL | DEPT | 1 | 4 | 4 |00:00:00.01 | 3 |
|* 3 | VIEW | | 4 | 4 | 14 |00:00:00.01 | 9 |
| 4 | TABLE ACCESS FULL| EMP | 4 | 14 | 56 |00:00:00.01 | 9 |
--------------------------------------------------------------------------------------
뷰가 inner table로 액세스될 때도 view 처리 단계에서 중간 집합을 생성하지 않는다.
dept에서 읽은만큼 emp 테이블을 fullscan하고 있다.
(총 buffer = 9이고 출력 건수는 56)
inline view에 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
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 14 |00:00:00.01 | 5 | | | |
| 1 | NESTED LOOPS | | 1 | 14 | 14 |00:00:00.01 | 5 | | | |
| 2 | TABLE ACCESS FULL | DEPT | 1 | 4 | 4 |00:00:00.01 | 3 | | | |
|* 3 | VIEW | | 4 | 4 | 14 |00:00:00.01 | 2 | | | |
| 4 | SORT ORDER BY | | 4 | 14 | 56 |00:00:00.01 | 2 | 2048 | 2048 | 2048 (0)|
| 5 | TABLE ACCESS FULL| EMP | 1 | 14 | 14 |00:00:00.01 | 2 | | | |
------------------------------------------------------------------------------------------------------------------
inline view에 order by를 추가했더니..
emp 테이블을 액세스하는 단계에서 14개의 row만 접근하였고 buffer 사용도 9->2로 줄었다.
sort를 위해 memory 사용량은 늘어났다.
emp 테이블을 한번만 full scan했고 소트 수행 후 PGA에 저장된 중간집합을 반복 액세스한 것을 알 수 있다.