GROUP BY 절을 포함한 뷰에 대한 조건절 PUSHDOWN
select deptno, avg_sal
from (select /*+no_merge */ deptno, avg(sal) avg_sal from emp group by deptno) a
where deptno = 30
--------------------------------------------------------------
| Id | Operation | Name | E-Rows |
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | VIEW | | 1 |
| 2 | SORT GROUP BY NOSORT | | 1 |
| 3 | TABLE ACCESS BY INDEX ROWID| EMP | 6 |
|* 4 | INDEX RANGE SCAN | EMP_DEEPTNO | 6 |
--------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("DEPTNO"=30)
-> 뷰머징에 실패했지만 옵티마이저가 조건절을 뷰 안쪽으로 밀어넣음으로써 인덱스 스캔한 것을 알 수 있다.
Predicate Information에서는 deptno = 30 조건으로 access한 것을 알 수 있다.
만약 조건절 pushing이 작동하지 않았다면 필터링 조건으로 deptno = 30이 실행되었을 것이다.
select /*+ no_merge(a)*/
b.deptno, b.dname, a.avg_sal
from (select deptno, avg(sal) avg_sal from emp group by deptno) a
, dept b
where a.deptno = b.deptno
and b.deptno = 30
------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | NESTED LOOPS | | 1 | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | | | |
|* 3 | INDEX UNIQUE SCAN | DEPT_PK | 1 | | | |
| 4 | VIEW | | 1 | | | |
| 5 | SORT GROUP BY | | 1 | 2048 | 2048 | 2048 (0)|
| 6 | TABLE ACCESS BY INDEX ROWID| EMP | 118 | | | |
|* 7 | INDEX RANGE SCAN | EMP_DEEPTNO | 118 | | | |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("B"."DEPTNO"=30)
7 - access("DEPTNO"=30)
조인조건 pushdown 과 잘 구분해야한다. NL조인을 수행하는 동안 DEPT 테이블에서 읽은 조인 컬럼을 건건이 뷰안으로
pushdown 한 것이 아니다.
UNION 집합 연산자를 포함한 뷰에 대한 조건절 Pushdown
union 집합 연산자를 포함한 뷰는 기본적으로 non-mergeable view이므로 뷰 merging이 안 된다.
따라서 조건절 pushing을 통해서만 최적화가 가능하다.
select *
from (select deptno, empno, ename, job, sal, sal * 1.1 sal2, hiredate
from emp
where job = 'CLERK'
union all
select deptno, empno, ename, job, sal, sal * 1.2 sal2, hiredate
from emp
where job = 'MANAGER' ) v
where v.deptno = 30;
---------------------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | VIEW | | 2 |
| 2 | UNION-ALL | | |
| 3 | TABLE ACCESS BY INDEX ROWID| EMP | 1 |
|* 4 | INDEX RANGE SCAN | EMP_X1 | 1 |
| 5 | TABLE ACCESS BY INDEX ROWID| EMP | 1 |
|* 6 | INDEX RANGE SCAN | EMP_X1 | 1 |
---------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("DEPTNO"=30 AND "JOB"='CLERK')
6 - access("DEPTNO"=30 AND "JOB"='MANAGER')
인덱스가 deptno + job컬럼의 순서대로 되어있을 때 위와 같이 해당 deptno + job 인덱스를 통해 데이터에 access 한 것을 알 수 있다.
아래는 조인 조건을 타고 전이된 상수 조건이 뷰 쿼리 블록에 Pushing 된 경우이다.
select /*+ ordered use_nl(e) */ d.dname, e.*
from dept d
,(select deptno, empno, ename, job, sal, sal * 1.1 sal2, hiredate from emp
where job = 'CLERK'
union all
select deptno, empno, ename, job, sal, sal * 1.2 sal2, hiredate from emp
where job = 'MANAGER' ) e
where e.deptno = d.deptno
and d.deptno = 30;
-----------------------------------------------------------
| Id | Operation | Name | E-Rows |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | NESTED LOOPS | | 2 |
| 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 |
|* 3 | INDEX UNIQUE SCAN | DEPT_PK | 1 |
| 4 | VIEW | | 2 |
| 5 | UNION-ALL | | |
| 6 | TABLE ACCESS BY INDEX ROWID| EMP | 1 |
|* 7 | INDEX RANGE SCAN | EMP_X1 | 1 |
| 8 | TABLE ACCESS BY INDEX ROWID| EMP | 1 |
|* 9 | INDEX RANGE SCAN | EMP_X1 | 1 |
-----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("D"."DEPTNO"=30)
7 - access("DEPTNO"=30 AND "JOB"='CLERK')
9 - access("DEPTNO"=30 AND "JOB"='MANAGER')
예시
select * from
(select deptno, avg(sal) from emp where deptno = 10 group by deptno) e1
,(select deptno, min(sal), max(sal) from emp group by deptno) e2
where e1.deptno = e2.deptno ;
---------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
|* 1 | HASH JOIN | | 1 | 963K| 963K| 405K (0)|
| 2 | VIEW | | 1 | | | |
| 3 | HASH GROUP BY | | 1 | 899K| 899K| 466K (0)|
| 4 | TABLE ACCESS BY INDEX ROWID| EMP | 98 | | | |
|* 5 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 98 | | | |
| 6 | VIEW | | 1 | | | |
| 7 | HASH GROUP BY | | 1 | 838K| 838K| 465K (0)|
| 8 | TABLE ACCESS BY INDEX ROWID| EMP | 98 | | | |
|* 9 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 98 | | | |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E1"."DEPTNO"="E2"."DEPTNO")
5 - access("DEPTNO"=10)
9 - access("DEPTNO"=10)
e1의 deptno = 10 조건이 e2의 엑세스 조건으로 사용되었다.
아래와 같이 쿼리 변환이 된 것을 알 수있다.
select * from
(select deptno, avg(sal) from emp where deptno = 10 group by deptno) e1
,(select deptno, min(sal), max(sal) from emp where deptno = 10 group by deptno) e2
where e1.deptno = e2.deptno ;
이 기능이 사용되지 않으면 다음과 같은 비효율이 발생한다.
select /*+ opt_param('_pred_move_around', 'false') */ * from
(select deptno, avg(sal) from emp where deptno = 10 group by deptno) e1
,(select deptno, min(sal), max(sal) avg_sal from emp group by deptno) e2
where e1.deptno = e2.deptno;
----------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | NESTED LOOPS | | 1 | | | |
| 2 | VIEW | | 1 | | | |
| 3 | HASH GROUP BY | | 1 | 899K| 899K| 728K (0)|
| 4 | TABLE ACCESS BY INDEX ROWID | EMP | 98 | | | |
|* 5 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 98 | | | |
| 6 | VIEW PUSHED PREDICATE | | 1 | | | |
|* 7 | FILTER | | | | | |
| 8 | SORT AGGREGATE | | 1 | | | |
| 9 | TABLE ACCESS BY INDEX ROWID| EMP | 98 | | | |
|* 10 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 98 | | | |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("DEPTNO"=10)
7 - filter(COUNT(*)>0)
10 - access("DEPTNO"="E1"."DEPTNO")
예시
select /*+ no_merge(e) push_pred(e) */ *
from dept d, (select empno, ename, deptno from emp) e
where e.deptno(+) = d.deptno
and d.loc = 'CHICAGO';
-----------------------------------------------------------------
| Id | Operation | Name | E-Rows |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | NESTED LOOPS OUTER | | 98 |
|* 2 | TABLE ACCESS FULL | DEPT | 1 |
| 3 | VIEW PUSHED PREDICATE | | 1 |
| 4 | TABLE ACCESS BY INDEX ROWID| EMP | 98 |
|* 5 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 98 |
-----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("D"."LOC"='CHICAGO')
5 - access("DEPTNO"="D"."DEPTNO") -> 인라인뷰안에서 d.deptno 컬럼을 참조하고 있는 것을 알 수 있다.
GROUP BY 절을 포함한 뷰에 대한 조인 조건 Pushdown
11g에서 제공하기 시작
select /*+ leading(d) use_nl(e) no_merge(e) push_pred(e) index(e (deptno)) */
d.deptno, d.dname, e.avg_sal
from dept d
, (select deptno, avg(sal) avg_sal from emp group by deptno) e
where e.deptno(+) = d.deptno;
10g버전으로 테스트했을 때.
alter session set optimizer_features_enable = '10.2.0.3'
-------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | NESTED LOOPS OUTER | | 4 | | | |
| 2 | TABLE ACCESS FULL | DEPT | 4 | | | |
|* 3 | VIEW | | 1 | | | |
| 4 | SORT GROUP BY | | 999 | 86016 | 86016 |75776 (0)|
| 5 | TABLE ACCESS FULL| EMP | 98000 | | | |
-------------------------------------------------------------------------
11g버전으로 테스트했을 때.
alter session set optimizer_features_enable = '11.2.0.3'
---------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | NESTED LOOPS OUTER | | 4 | | | |
| 2 | TABLE ACCESS FULL | DEPT | 4 | | | |
| 3 | VIEW PUSHED PREDICATE | | 1 | | | |
| 4 | SORT GROUP BY | | 1 | 2048 | 2048 | 2048 (0)|
| 5 | TABLE ACCESS BY INDEX ROWID| EMP | 98 | | | |
|* 6 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 98 | | | |
---------------------------------------------------------------------------------------------
dept 테이블에로부터 deptno값을 전달받아 인덱스를 활용하는 것을 알 수 있다.
UNION 집합 연산을 포함한 뷰에 대한 조인 조건 Pushdown
select /*+ push_pred(e) */ d.dname, e.*
from dept d
,(select deptno, empno, ename, job, sal, sal * 1.1 sal2, hiredate from emp
where job = 'CLERK'
union all
select deptno, empno, ename, job, sal, sal * 1.2 sal2, hiredate from emp
where job = 'SALESMAN' ) e
where e.deptno = d.deptno
and d.loc = 'CHICAGO'
------------------------------------------------------------------
| Id | Operation | Name | E-Rows |
------------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | NESTED LOOPS | | 39 |
|* 2 | TABLE ACCESS FULL | DEPT | 1 |
| 3 | VIEW | | 1 |
| 4 | UNION ALL PUSHED PREDICATE | | |
|* 5 | TABLE ACCESS BY INDEX ROWID| EMP | 20 |
|* 6 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 98 |
|* 7 | TABLE ACCESS BY INDEX ROWID| EMP | 20 |
|* 8 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 98 |
------------------------------------------------------------------
마찬가지로 dept의 deptno 값을 전달받아 인덱스를 활용하는 것을 알 수 있다.
Outer 조인 뷰에 대한 조인 조건 Pushdown
1. 뷰 안에서 참조하는 테이블이 단 하나일 때, 뷰 Merging을 시도
2. 뷰 안에서 참조하는 테이블이 두 개 이상일 때, 조인 조건식을 뷰 안쪽으로 Pushing하려고 시도
select /*+ push_pred(b) */
a.empno, a.ename, a.sal, a.hiredate, b.deptno, b.dname, b.loc, a.job
from emp a
,(select e.empno, d.deptno, d.dname, d.loc
from emp e, dept d
where d.deptno = e.deptno
and e.sal >= 1000
and d.loc in ( 'CHICAGO', 'NEW YORK' ) ) b
where b.empno(+) = a.empno
and a.hiredate >= to_date('19810901', 'yyyymmdd');
-----------------------------------------------------------
| Id | Operation | Name | E-Rows |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | NESTED LOOPS OUTER | | 72094 |
|* 2 | TABLE ACCESS FULL | EMP | 72094 |
| 3 | VIEW PUSHED PREDICATE | | 1 |
| 4 | NESTED LOOPS | | 1 |
|* 5 | TABLE ACCESS BY INDEX ROWID| EMP | 1 |
|* 6 | INDEX UNIQUE SCAN | EMP_PK | 1 |
|* 7 | TABLE ACCESS BY INDEX ROWID| DEPT | 2 |
|* 8 | INDEX UNIQUE SCAN | DEPT_PK | 1 |
-----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("A"."HIREDATE">=TO_DATE(' 1981-09-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))
5 - filter("E"."SAL">=1000)
6 - access("E"."EMPNO"="A"."EMPNO")
7 - filter(("D"."LOC"='CHICAGO' OR "D"."LOC"='NEW YORK'))
8 - access("D"."DEPTNO"="E"."DEPTNO")