- 이 문서는 구루비에서 작성하였습니다.
- 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=4949187&
- 구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.
조건절 Pushing
- 옵티마이져가 뷰를 액세스하는 쿼리를 최적화하는 단계
- View Merging
? 쿼리내부의 인라인뷰/뷰를 풀어 메인쿼리와 같은 레벨의 쿼리블록으로 만드는 쿼리 변환기능이다. (작은 단위의 쿼리블록보다 Merging/Unest된 경우 쿼리 최적화를 위한 다양한 cost계산을 할 수 있기 때문) - Push Predicate
? 해당 뷰를 참조하는 쿼리블록의 조건절을 뷰안의 쿼리로 제공(push)하는 기능임
- View Merging
- 뷰머징이 실패할 경우
- 복합 뷰(Complex View) Merging기능이 비활성화
- 사용자가 No_merge힌트를 사용한 경우
- 뷰안에 Rownum Psedo컬럼이 있는 경우(조건절 Pushing도 되지 않음.)
- 분석함수를 사용한 경우(조건절 Pushing도 되지 않음.)
- 비용기반 쿼리 변환이 작동해 No Merging을 선택한 경우
- Non-mergeable Views : 뷰 Merging이 시행되면 부정확한 결과가 나올 경우?
- 조건절 Pushing의 종류
- 조건절(Predicate) Pushdown
? 쿼리블록 밖에 있는 조건들을 쿼리 블록 안쪽으로 밀어 넣는 것을 말함 - 조건절(Predicate) Pullup
? 쿼리 블록 안에 있는 조건들을 쿼리 블록 밖으로 내와서 다른 쿼리블록에 Pushdown하는데 사용( Predicate Move Around) - 조인조건(Join Predicate) Pushdown
? NL조인 수행 중에 드라이빙테이블에서 읽은 값을 건건이 Inner쪽으로 밀어 넣는 것을 말함
- 조건절(Predicate) Pushdown
관련 힌트와 파라미터 :
-
- /*+ push_pred(table_name/alias) */
- /*+ no_push_pred(table_name/alias) */
- /*+ opt_param('_optimizer_push_pred_cost_based', 'false') */
- /*+ opt_param('_push_join_predicate', 'false') */
- /*+ opt_param('_push_join_union_view', 'false') */
- /*+ opt_param('_push_join_union_view2', 'false') */
Be Careful - 조건절Pushdown/Pullup은 항상 더 나은 성능을 보장함
- 조인조건Pushdown은 NL조인을 전제로함, NL의 특성상 성능저하될 수 있어 제어힌트 제공
- 조인조건Pushdown은 NL조인을 전제로 하기 때문에 굳이 use_nl힌트를 줄 필요는 없다.
- 9i에서는 push_pred와 use_nl힌트를 함께 사용할 때 pushdown기능이 작동하지 않을 수 있다.
조건절 Pushdown
Group By 절을 포함한 뷰에 대한 Pushdown
================================================================ primary key : dept_pk on dept(deptno); index : emp_deptno_idx on emp(deptno); ? 단순한 Group By 뷰에 조건절이 파고든 예 ? 뷰 내부에 조건절이 없었지만 Predicate정보에 보면 access("DEPTNO"=30)로 조건이 뷰 안에서 실행된 것을 확인할 수 있다. orcl:WOONG > 1 select deptno, avg_sal 2 from (select deptno, avg(sal) avg_sal from emp group by deptno) a 3 where deptno = 30; 경 과: 00:00:00.00 Execution Plan ---------------------------------------------------------- Plan hash value: 1032861127 ---------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | ---------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 7 | | 1 | SORT GROUP BY NOSORT | | 1 | 7 | | 2 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 35 | |* 3 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 5 | | ---------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("DEPTNO"=30)
Group By절이 포함되어 있는 뷰에 대한 조인을 했을 경우의 예
뷰 merging이 되지 않도록 no_merge힌트로 제어한 후 테스트함 단순한 Group By 뷰에 파고든 예와 같이 뷰안에서 조건이 수행된 것을 확인할 수 있다. orcl:WOONG > 1 select /*+ no_merge(a) */ 2 b.deptno, b.dname, a.avg_sal 3 from (select deptno, avg(sal) avg_sal from emp group by deptno) a 4 , dept b 5 where a.deptno = b.deptno 6 and b.deptno = 30; 경 과: 00:00:00.00 Execution Plan ---------------------------------------------------------- Plan hash value: 1855526360 ------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 28 | | 1 | NESTED LOOPS | | 1 | 28 | | 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 13 | |* 3 | INDEX UNIQUE SCAN | DEPT_PK | 1 | | | 4 | VIEW | | 1 | 15 | | 5 | SORT GROUP BY | | 1 | 10 | | 6 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 50 | |* 7 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 5 | | ------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("B"."DEPTNO"=30) 7 - access("DEPTNO"=30) 실제로는 아래처럼 '조건절 이행'이 먼저 일어났다. 조인조건에 의해서 내부적으로 조건절이 추가되어 집합A로 파고들 수 있었기 때문이다. orcl:WOONG > 1 select /*+ no_merge(a) */ 2 b.deptno, b.dname, a.avg_sal 3 from (select deptno, avg(sal) avg_sal from emp group by deptno) a 4 , dept b 5 where a.deptno = b.deptno 6 and b.deptno = 30 7 and a.deptno = 30; 경 과: 00:00:00.00 Execution Plan ---------------------------------------------------------- Plan hash value: 1855526360 ------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 39 | | 1 | NESTED LOOPS | | 1 | 39 | | 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 13 | |* 3 | INDEX UNIQUE SCAN | DEPT_PK | 1 | | | 4 | VIEW | | 1 | 26 | | 5 | SORT GROUP BY | | 1 | 10 | | 6 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 50 | |* 7 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 5 | | ------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("B"."DEPTNO"=30) 7 - access("DEPTNO"=30) 결국 조인컬럼이 가공되어 조건절 이행이 일어날 수 없다면 Pushdown은 일어날 수 없다. orcl:WOONG > 1 select /*+ no_merge(a) */ 2 b.deptno, b.dname, a.avg_sal 3 from (select deptno||'' deptno 4 , avg(sal) avg_sal from emp group by deptno) a 5 , dept b 6 where a.deptno = b.deptno 7 and b.deptno = 30; 경 과: 00:00:00.00 Execution Plan ---------------------------------------------------------- Plan hash value: 4150857296 ---------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | ---------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 53 | | 1 | NESTED LOOPS | | 1 | 53 | | 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 16 | |* 3 | INDEX UNIQUE SCAN | DEPT_PK | 1 | | | 4 | VIEW | | 1 | 37 | | 5 | SORT GROUP BY | | 1 | 7 | |* 6 | TABLE ACCESS FULL | EMP | 1 | 7 | ---------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("B"."DEPTNO"=30) 6 - filter(TO_NUMBER(TO_CHAR("DEPTNO")||'')=30) ================================================================
Union 집합연산자를 포함한 Pushdown
================================================================ primary key : dept_pk on dept(deptno); index : emp_x1 on emp(deptno, job); Union/Minus같은 집합연산자를 포함하는 뷰는 뷰 Merging이 되지 않으므로 Pushdown으로만 최적화 될 수 있다. 뷰 merging이 되지 않도록 no_merge힌트로 제어한 후 테스트함 뷰내부의 조건절은 job만 존재하였으나 메인쿼리의 조건(v.deptno = 30)이 파고들어 뷰 안에서 조건에 따른 결합인덱스(emp_x1)를 이용한 처리를 한 것을 확인할 수 있다. orcl:WOONG > 1 select * 2 from (select deptno, empno, ename, job, sal, sal * 1.1 sal2, hiredate 3 from emp 4 where job = 'CLERK' 5 union all 6 select deptno, empno, ename, job, sal, sal * 1.2 sal2, hiredate 7 from emp 8 where job = 'SALESMAN' ) v 9 where v.deptno = 30; 경 과: 00:00:00.00 Execution Plan ---------------------------------------------------------- Plan hash value: 3488565791 --------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | --------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 148 | | 1 | VIEW | | 2 | 148 | | 2 | UNION-ALL | | | | | 3 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 33 | |* 4 | INDEX RANGE SCAN | EMP_X1 | 2 | | | 5 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 33 | |* 6 | INDEX RANGE SCAN | EMP_X1 | 2 | | --------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("DEPTNO"=30 AND "JOB"='CLERK') 6 - access("DEPTNO"=30 AND "JOB"='SALESMAN') Union/Minus같은 집합연산자를 포함하는 뷰는 뷰 Merging이 되지 않으므로 Pushdown으로만 최적화 될 수 있다. 뷰 merging이 되지 않도록 no_merge힌트로 제어한 후 테스트함 뷰내부의 조건절은 job만 존재하였으나 메인쿼리의 조건(v.deptno = 30)이 파고들어 뷰 안에서 조건에 따른 결합인덱스(emp_x1)를 이용한 처리를 한 것을 확인할 수 있다. orcl:WOONG > 1 select /*+ ordered use_nl(e) */ d.dname, e.* 2 from dept d 3 ,(select deptno, empno, ename, job, sal, sal * 1.1 sal2, hiredate from emp 4 where job = 'CLERK' 5 union all 6 select deptno, empno, ename, job, sal, sal * 1.2 sal2, hiredate from emp 7 where job = 'SALESMAN' ) e 8 where e.deptno = d.deptno 9 and d.deptno = 30; 경 과: 00:00:00.00 Execution Plan ---------------------------------------------------------- Plan hash value: 1945841114 ----------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | ----------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 174 | | 1 | NESTED LOOPS | | 2 | 174 | | 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 13 | |* 3 | INDEX UNIQUE SCAN | DEPT_PK | 1 | | | 4 | VIEW | | 2 | 148 | | 5 | UNION-ALL | | | | | 6 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 33 | |* 7 | INDEX RANGE SCAN | EMP_X1 | 2 | | | 8 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 33 | |* 9 | INDEX RANGE SCAN | EMP_X1 | 2 | | ----------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("D"."DEPTNO"=30) 7 - access("DEPTNO"=30 AND "JOB"='CLERK') 9 - access("DEPTNO"=30 AND "JOB"='SALESMAN') ================================================================
조건절 Pullup
================================================================ primary key : dept_pk on dept(deptno); index : emp_deptno_idx on emp(deptno); orcl:WOONG > 1 select * from 2 (select deptno, avg(sal) from emp where deptno = 10 group by deptno) e1 3 ,(select deptno, min(sal), max(sal) from emp group by deptno) e2 4 where e1.deptno = e2.deptno ; 경 과: 00:00:00.00 Execution Plan ---------------------------------------------------------- Plan hash value: 1076936357 ------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | ------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 69 | |* 1 | HASH JOIN | | 1 | 69 | | 2 | VIEW | | 1 | 28 | | 3 | HASH GROUP BY | | 1 | 10 | | 4 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 50 | |* 5 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 5 | | | 6 | VIEW | | 3 | 123 | | 7 | HASH GROUP BY | | 3 | 30 | | 8 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 50 | |* 9 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 5 | | ------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("E1"."DEPTNO"="E2"."DEPTNO") 5 - access("DEPTNO"=10) 9 - access("DEPTNO"=10) Predicate Information을 보면 두개의 인라인뷰 중에 where deptno = 10 조건을 가진 뷰는 하나지만 다른 한쪽 인라인뷰에도 조건이 파고들어 처리된 것을 확인할 수 있다. 마치 아래와 같은 쿼리로 실행된 것 처럼. 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 ; ================================================================
조인조건 Pushdown
인라인뷰 e에는 d.deptno에 대한 참조가 없음에도 실행계획에는 조인조건이 들어간 것과 같은 Pushdown효과를 보이고 있다. 실행계획에 VIEW PUSHED PREDICATE 오퍼레이션을 확인할 수 있다. orcl:WOONG > 1 select /*+ no_merge(e) push_pred(e) */ * 2 from dept d, (select empno, ename, deptno from emp) e 3 where e.deptno(+) = d.deptno 4 and d.loc = 'CHICAGO'; 경 과: 00:00:00.00 Execution Plan ---------------------------------------------------------- Plan hash value: 3116586712 ------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | ------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 4 | 220 | | 1 | NESTED LOOPS OUTER | | 4 | 220 | |* 2 | TABLE ACCESS FULL | DEPT | 1 | 20 | | 3 | VIEW PUSHED PREDICATE | | 1 | 35 | | 4 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 80 | |* 5 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 5 | | ------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("D"."LOC"='CHICAGO') 5 - access("DEPTNO"="D"."DEPTNO")
Group By 절을 포함한 뷰에 대한 Pushdown
=============================================================== 10g이전에는 Group By를 포함하는 뷰에 대한 조인조건 pushdown기능이 지원되지 않는다.(?) 11g부터 가능함 아래 예제처럼 Outer관계라면 스칼라쿼리로 변환할 수 있으며 이때 여러 컬럼을 참조한다면 Type오브젝트를 사용하거나 Substr함수로 잘라서 사용할 수 있다. orcl:WOONG > 1 select /*+ leading(d) use_nl(e) no_merge(e) push_pred(e) index(e (deptno)) */ 2 d.deptno, d.dname, e.avg_sal 3 from dept d 4 , (select deptno, avg(sal) avg_sal from emp group by deptno) e 5 where e.deptno = d.deptno; 경 과: 00:00:00.00 Execution Plan ---------------------------------------------------------- Plan hash value: 1993021132 ---------------------------------------------------- | Id | Operation | Name | Rows | Bytes | ---------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 117 | | 1 | NESTED LOOPS | | 3 | 117 | | 2 | TABLE ACCESS FULL | DEPT | 4 | 52 | |* 3 | VIEW | | 1 | 26 | | 4 | SORT GROUP BY | | 3 | 21 | | 5 | TABLE ACCESS FULL| EMP | 14 | 98 | ---------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("E"."DEPTNO"="D"."DEPTNO") ===============================================================
Union 집합연산자를 포함한 Pushdown
=============================================================== 뷰내부의 조건절은 job만 존재하였으나 메인쿼리의 조건(d.loc = 'CHICAGO')이 파고들어 뷰 안에서 조건에 따른 결합인덱스(emp_x1)를 이용한 처리를 한 것을 확인할 수 있다. orcl:WOONG > 1 select /*+ push_pred(e) */ d.dname, e.* 2 from dept d 3 ,(select deptno, empno, ename, job, sal, sal * 1.1 sal2, hiredate from emp 4 where job = 'CLERK' 5 union all 6 select deptno, empno, ename, job, sal, sal * 1.2 sal2, hiredate from emp 7 where job = 'SALESMAN' ) e 8 where e.deptno = d.deptno 9 and d.loc = 'CHICAGO'; 경 과: 00:00:00.00 Execution Plan ---------------------------------------------------------- Plan hash value: 4023361524 ------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | ------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 2 | 200 | | 1 | NESTED LOOPS | | 2 | 200 | | 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 20 | |* 3 | INDEX RANGE SCAN | DEPT_IDX | 1 | | | 4 | VIEW | | 1 | 80 | | 5 | UNION ALL PUSHED PREDICATE | | | | | 6 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 36 | |* 7 | INDEX RANGE SCAN | EMP_IDX | 2 | | | 8 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 36 | |* 9 | INDEX RANGE SCAN | EMP_IDX | 2 | | ------------------------------------------------------------------ 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') ===============================================================
Outer 조인 뷰에 대한 조인조건 Pushdown
- Outer 조인에서 Inner쪽 집합이 뷰 쿼리일 때 테이블개수에 따라 옵티마이져는 2가지 방법 중 하나를 선택한다.
- 뷰 안에서 참조하는 테이블이 단 하나일 때, 뷰 머징을 시도한다.
- 뷰 안에서 참조하는 테이블이 2개 이상일 때 조인조건식을 Pushdown하려 한다.
- 뷰 내에서 참조하는 테이블 중 하나라도 no_merge로 뷰머징을 제한하면 Pushdown하려 한다.
=============================================================== orcl:WOONG > 1 select /*+ push_pred(b) */ 2 a.empno, a.ename, a.sal, a.hiredate, b.deptno, b.dname, b.loc, a.job 3 from emp a 4 ,(select e.empno, d.deptno, d.dname, d.loc 5 from emp e, dept d 6 where d.deptno = e.deptno 7 and e.sal >= 1000 8 and d.loc in ( 'CHICAGO', 'NEW YORK' ) ) b 9 where b.empno(+) = a.empno 10 and a.hiredate >= to_date('19810901', 'yyyymmdd'); 경 과: 00:00:00.00 Execution Plan ---------------------------------------------------------- Plan hash value: 3468508478 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 952 | | 1 | NESTED LOOPS OUTER | | 14 | 952 | | 2 | TABLE ACCESS BY INDEX ROWID | EMP | 14 | 476 | |* 3 | INDEX RANGE SCAN | EMP_HIREDATE_IDX | 14 | | | 4 | VIEW PUSHED PREDICATE | | 1 | 34 | | 5 | NESTED LOOPS | | 1 | 35 | |* 6 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 15 | |* 7 | INDEX UNIQUE SCAN | EMP_PK | 1 | | |* 8 | TABLE ACCESS BY INDEX ROWID| DEPT | 2 | 40 | |* 9 | INDEX UNIQUE SCAN | DEPT_PK | 1 | | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("A"."HIREDATE">=TO_DATE('1981-09-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) 6 - filter("E"."SAL">=1000) 7 - access("E"."EMPNO"="A"."EMPNO") 8 - filter("D"."LOC"='CHICAGO' OR "D"."LOC"='NEW YORK') 9 - access("D"."DEPTNO"="E"."DEPTNO") ===============================================================
문서에 대하여
- 최초작성자 : [이지웅]
- 최초작성일 : 2010년 05월 14일
- 이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
- 이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법II'를 참고하였습니다.
문서정보
- 이 문서는 구루비에서 작성하였습니다.
- 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=4949187&
- 구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.
Comments (2)
5월 14, 2010
이지웅 says:
아~ wiki포맷팅할라니 힘들다 ㅜ_ㅜ아~ wiki포맷팅할라니 힘들다 ㅜ_ㅜ
5월 14, 2010
임주영 says:
그래도 잘 하셨는데요.. ^^그래도 잘 하셨는데요.. ^^