옵티마이저는 포기하지 않고 2차적으로 조건절 (Predicate)Pushing을 시도한다. 이는 뷰를 참조하는 쿼리 블록의 조건절을 조건절을 뷰 쿼리 블록 안으로 Pushing하는 기능을 일컫는다.
관련 힌트와 파라미터
-rownum pseudo절 및 분석함수는 Non-mergeable, Non-pushable View이다(쿼리 결과가 달라지는 일이 발생하기 때문이다.)
SQL> alter session set "_complex_view_merging" = false;
SQL> create index emp_deptno_idx on emp( deptno );
인덱스가 생성되었습니다.
SQL> select deptno, avg_sal
from (select deptno, avg(sal) avg_sal from emp group by deptno ) a
where deptno = 30
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 2 (0)| 00:00:01 |
| 1 | VIEW | | 1 | 26 | 2 (0)| 00:00:01 |
| 2 | SORT GROUP BY NOSORT | | 1 | 7 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 35 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 5 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("DEPTNO"=30)
뷰 Merging에 실패했지만 옵티마이저가 조건절을 뷰 안쪽으로 밀어 넣음으로써, EMP_DEPTNO_IDX인덱스를 사용함.
조건절 Pushing이 작동 안 했다면 emp테이블을 Full Scan하고서 group by이후에 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 | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 39 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 13 | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
| 4 | VIEW | | 1 | 26 | 2 (0)| 00:00:01 |
| 5 | SORT GROUP BY | | 1 | 7 | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 35 | 2 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 5 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("B"."DEPTNO"=30)
7 - access("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
and a.deptno = 30; <= 조건절 이행 이 먼저 일어 난 후 이 상태에서 a.deptno = 30 조건절이 인라인 뷰 안쪽으로 Pushing됨
union집합 연산자를 포함한 뷰는 Non-mergeable View에 속하므로 복합 뷰Merging기능을 활성화하더라도 뷰 Merging에 실패한다.
따라서 조건절 Pushing을 통해서만 최적화가 가능하며, 아래는 그 사례를 보이고 있다.
SQL> alter session set "_complex_view_merging" = true;
세션이 변경되었습니다.
SQL> create index emp_x1 on emp(deptno, job);
인덱스가 생성되었습니다.
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.1 sal2, hiredate
from emp
where job = 'SALESAMAN' ) v
where v.deptno = 30;
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 148 | 4 (0)| 00:00:01 |
| 1 | VIEW | | 2 | 148 | 4 (0)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 33 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_X1 | 2 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 33 | 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | EMP_X1 | 2 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("DEPTNO"=30 AND "JOB"='CLERK')
6 - access("DEPTNO"=30 AND "JOB"='SALESAMAN')
아래는 조인 조건을 타고 전이된 상수 조건이 뷰 쿼리 블록에 Pushing된 경우다.
select *
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.1 sal2, hiredate
from emp
where job = 'SALESAMAN' ) e
where e.deptno = d.deptno
and d.deptno = 30;
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 188 | 5 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 2 | 188 | 5 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 20 | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
| 4 | VIEW | | 2 | 148 | 4 (0)| 00:00:01 |
| 5 | UNION-ALL | | | | | |
| 6 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 33 | 2 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | EMP_X1 | 2 | | 1 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 33 | 2 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | EMP_X1 | 2 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("D"."DEPTNO"=30)
7 - access("DEPTNO"=30 AND "JOB"='CLERK')
9 - access("DEPTNO"=30 AND "JOB"='SALESAMAN')
조건절을 쿼리 블록 안으로 밀어 넣을 뿐만 아니라 안쪽에 있는 조건들을 바깥 쪽으로 끄집어 내기도 하는데, 이를 조건절 Pullup이라고 한다.
그리고 그것을 다시 다른 쿼리 블록에 Pushdown하는 데 사용한다.
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 | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 65 | 5 (20)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 65 | 5 (20)| 00:00:01 |
| 2 | VIEW | | 1 | 26 | 2 (0)| 00:00:01 |
| 3 | HASH GROUP BY | | 1 | 7 | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 35 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 5 | | 1 (0)| 00:00:01 |
| 6 | VIEW | | 1 | 39 | 2 (0)| 00:00:01 |
| 7 | HASH GROUP BY | | 1 | 7 | 2 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 35 | 2 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 5 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E1"."DEPTNO"="E2"."DEPTNO")
5 - access("DEPTNO"=10)
9 - access("DEPTNO"=10)
인라인 뷰 e2에는 deptno = 10 조건이 없지만 Predicate정보를 보면 양쪽 모두 이 조건이 있다.
ex)아래는 Predicate Move Around기능이 작동하지 않았을 때 비효율을 보여준다.(Full Table Scan이 나타남)
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) from emp group by deptno ) e2
where e1.deptno = e2.deptno;
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 65 | 7 (29)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 65 | 7 (29)| 00:00:01 |
| 2 | VIEW | | 1 | 26 | 2 (0)| 00:00:01 |
| 3 | HASH GROUP BY | | 1 | 7 | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 35 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 5 | | 1 (0)| 00:00:01 |
| 6 | VIEW | | 3 | 117 | 4 (25)| 00:00:01 |
| 7 | HASH GROUP BY | | 3 | 21 | 4 (25)| 00:00:01 |
| 8 | TABLE ACCESS FULL | EMP | 14 | 98 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E1"."DEPTNO"="E2"."DEPTNO")
5 - access("DEPTNO"=10)
지금까지 보았던 조인문에서의 조건절 Pushdown은 상수 조건이 조인 조건을 타고 전이된 것을 Pushing하는 기능이었던 반면, 지금
설명하는 조인 조건 Pushdown은 조인을 수행하는 중에 드라이빙 집합에서 얻은 값을 뷰 쿼리 블록 안에 실시간을 Pushing하는 기능이다.
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 | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 220 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 4 | 220 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 20 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | DEPT_IDX | 1 | | 1 (0)| 00:00:01 |
| 4 | VIEW PUSHED PREDICATE | | 1 | 35 | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 80 | 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 5 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("D"."LOC"='CHICAGO')
6 - access("DEPTNO"="D"."DEPTNO")
인라인 뷰 내에서 메인 쿼리에 있는 D.DEPTNO컬럼을 참조할 수 없음에도 옵티마이저가 이를 참조하는 조인 조건을 뷰 안쪽에 생성해준 것을 알 수 있다.
힌트:
push_pred : 조인 조건 Pushdown을 유도한다.
no_push_pred : 조인 조건 Pushdown을 방지한다.
파라미터(10g 기준) :
_push_join_predicate : 뷰 Merging에 실패한 뷰 안쪽으로 조인 조건을 Pushdown하는 기능을 활성화한다. union 또는 union all을 포함하는 Non-mergeable뷰에 대해서는
아래 두 파라미터가 따로 제공된다.
_push_join_union_view : union all을 포함하는 Non-mergeable View안쪽으로 조인 조건을 Pushdown하는 기능을 활성화한다.
_push_join_union_view2 : union 을 포함하는 Non-mergeable View안쪽으로 조인 조건을 Pushdown하는 기능을 활성화한다.
GROUP BY절을 포함한 뷰에 대한 조인 조건 Pushdown
group by를 포함하는 뷰에 대한 조인 조건 Pushdown기능은 11g에 와서야 제공되기 시작함
10g실행계획 조인 조건 Pushdown이 작동하지 않아 emp쪽 인덱스를 full scan하는 것을 확인하기 바람.(dept테이블레서 읽히는 deptno마다 emp테이블 전체를 group by 함)
select /*+ leading(d) use_nl(e) no_mergo(e) push_pred(e) */ *
from dept d, (select deptno, avg(sal) avg_sal from emp group by deptno ) e
where e.deptno(+) = d.deptno;
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 184 | 19 (22)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 4 | 184 | 19 (22)| 00:00:01 |
| 2 | TABLE ACCESS FULL | DEPT | 4 | 80 | 3 (0)| 00:00:01 |
|* 3 | VIEW | | 1 | 26 | 4 (25)| 00:00:01 |
| 4 | SORT GROUP BY | | 3 | 21 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL| EMP | 14 | 98 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("E"."DEPTNO"(+)="D"."DEPTNO")
UNION 집합 연산을 포함한 뷰에 대한 조인 조건 Pushdown
union또는 union all을 포함한 뷰 쿼리 블록에 대한 조인 조건 Pushdown은 10g 이전부터 제공되던 기능이다.
SQL> create index dept_idx on dept(loc);
인덱스가 생성되었습니다.
SQL> create index emp_idx on emp( deptno, job );
인덱스가 생성되었습니다.
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.1 sal2, hiredate from emp
where job = 'SALESMAN'
) e
where e.deptno = d.deptno
and d.loc = 'CHICAGO'
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 200 | 6 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 2 | 200 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 20 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | DEPT_IDX | 1 | | 1 (0)| 00:00:01 |
| 4 | VIEW | | 1 | 80 | 4 (0)| 00:00:01 |
| 5 | UNION ALL PUSHED PREDICATE | | | | | |
| 6 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 36 | 2 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | EMP_IDX | 2 | | 1 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 36 | 2 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | EMP_IDX | 2 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("D"."LOC"='CHICAGO')
7 - access("DEPTNO"="D"."DEPTNO" AND "JOB"='CLERK')
9 - access("DEPTNO"="D"."DEPTNO" AND "JOB"='SALESMAN')
emp_idx인덱스는 [deptno + job]순으로 구성된 결합 인덱스고, 인덱스 선두컬럼인 deptno조건이 뷰 쿼리 블록 안쪽에 기술되지 않았음에도 이 인덱스가 정상적인
Range Scan을 보이고 있다. loc='CHICAGO'조건에 해당하는 DEPT테이블을 스캔하면서 얻은 deptno값을 뷰 쿼리 블록 안에 제공했기 때문이며, 실행계획 아래쪽
Predicate정보를 통해서도 deptno조건이 인덱스 액세스 조건으로 사용되었음을 알 수 있다.
Outer 조인 뷰에 대한 조인 조건 Pushdown
Outer조인에서 Inner쪽 집합이 뷰 쿼리 블록일 때, 뷰 안에서 참조하는 테이블 개수에 따라 옵티마이져는 다음 2가지 방법 중 하나를 선택한다.
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 | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 952 | 31 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 14 | 952 | 31 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | EMP | 14 | 476 | 3 (0)| 00:00:01 |
| 3 | VIEW PUSHED PREDICATE | | 1 | 34 | 2 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 35 | 2 (0)| 00:00:01 |
|* 5 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 15 | 1 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
|* 7 | TABLE ACCESS BY INDEX ROWID| DEPT | 2 | 40 | 1 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("A"."HIREDATE">=TO_DATE('1981-09-01 00:00:00', 'yyyy-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")