오라클 성능 고도화 원리와 해법 II (2016년)
목록
조건절 Pushing 0 0 4,396

by 구루비 쿼리변환 쿼리변환 쿼리변환 PUSHING PUSHDOWN [2017.05.27]

04.조건절 Pushing

  • 뷰머징 실패시 옵티마이저는 조건절(Predicate) Pushing을 시도한다.
    뷰를 참조하는 쿼리 블록의 조건절을 뷰 쿼리 블록 안으로 Pushing 하는 기능이다.
  • 조건절 Pushing의 종류
    • 조건절(Predicate) Pushdown : 쿼리 블록 밖에 있는 조건을 쿼리 블록 안쪽으로 밀어 넣는 것
    • 조건절(Predicate) Pullup : 쿼리 블록 안에 있는 조건을 쿼리 블록 밖으로 내오는 것
    • 조인 조건(Join Predicate) Pushdown : NL 조인 수행 중에 드라이빙 테이블에서 읽은 값을 건건이 inner 쪽 뷰 쿼리 블록 안으로 밀어 넣는 것
  • 뷰 안에 rownum이나 분석함수가 있을 경우 push 불가하다.
  • 관련 힌트와 파라미터
    • select /*+ opt_param('_optimizer_push_pred_cost_based','false') */ * from ~
    • alter system set "_optimizer_push_pred_cost_based" = false;

조건절 pushdown

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')

조건절 Pullup

  • 조건절을 쿼리 블록 안으로 밀어 넣을 뿐만 아니라 안쪽에 있는 조건들을 바깥 쪽으로 끄집어 내기도하는데
    이를 조건절(Predicate) Pullup'이라고 한다.

예시


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")

조인조건 Pushdown

  • 조인 조건절을 뷰 쿼리 블록 안으로 밀어넣는 것으로서, NL 조인 수행 중에 드라이빙 테이블에서 읽은 조인 컬럼 값을
    Inner쪽 뷰 쿼리 블록 내에서 참조할 수 있도록 하는 기능

예시


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 컬럼을 참조하고 있는 것을 알 수 있다.
  • pushdown을 제어하는 힌트
    • push_pred : 조인 조건 pushdown을 유도
    • no_push_pred : 조인 조건 pushdown을 방지
  • 제어 파라미터
    • _push_join_predicate : 뷰 Merging에 실패한 뷰 안쪽으로 조인 조건을 Pushdown하는 기능 활성화
    • _push_join_union_view : uion all을 포함하는 Non-mergeable View 안쪽으로 조인 조건을 Pushdown 하는 기능을 활성화
    • _push_join_union_view2 : union 을 포함하는 Non-mergeable View 안쪽으로 조인 조건을 Pushdown하는 기능을 활성화

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")
"구루비 주주클럽 스터디모임" 에서 2016년에 "오라클 성능 고도화 원리와 해법 II " 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/3360

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

목록
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입