4. 조건절 Pushing

조회 수 6058 추천 수 0 2011.06.02 15:32:09
실천하자 *.18.78.25

4. 조건절 Pushing

  • 뷰를 액세스하는 쿼리 최적화 시, 옵티마이져는 1차적으로 뷰 Merging 고려
  • 뷰 Merging 실패조건
    • 복합 뷰(Complex View)Merging 기능이 활성화
    • 사용자가 no_merge 힌트사용
    • Non-mergeable Views : 뷰 Merging 시행하면 부정확한 결과 가능성
    • 비용기반 쿼리 변환이 작동해 NO Merging 선택(10g 이후)

  • 2차적으로 조건절 Pushing 수행
  • 조건절 Pushing
    • 정의 : 뷰를 참조하는 쿼리 블록의 조건절을 뷰 쿼리 블록 안으로 Pushing
    • 장점 : 뷰 안 처리 일량을 최소화, 리턴 결과 건수 감소로 다음 단계 처리해야 할 일량 감소


조건절 Pushing 종류

  • 조건절(Predicate) Pushdown : 쿼리 블록 밖에 있는 조건들을 쿼리 블록 안쪽으로 밀어 넣는 것
  • 조건절(Predicate) Pullup : 쿼리 블록 안에 있는 조건들을 쿼리 블록 밖으로 내와서 다시 다른 쿼리 블록 Pushdown
  • 조인 조건(Join Predicate) Pushdown : NL 조인 수행 중 드라이빙 테이블에서 읽은 값 건건이 Inner 쪽(=right side) 뷰 쿼리 블록 안으로 밀어 넣는 것

관련 힌트와 파라미터

  • 조건절 Pushdown과 Pullup은 항상 더 나은 성능 보장으로 별도의 힌트 제공하지 않음
  • 조인 조건(Join Predicate) Pushdown은 NL조인적용으로 성능이 나빠질 수 있음 
    • ☞ 제어 힌트 제공 : push_pred, no_push_pred

Non-pushable View




(1) 조건절 Pushdown


GROUP BY 절을 포함한 뷰에 대한 조건절 Pushdown

  • group by절 포함 복합 뷰 머징에 실패 시, 쿼리 블록 밖에 있는 조건절을 쿼리 블록 안쪽에 밀어 넣어 group by 데이터량 줄임
  • 인덱스 상황에 따라서 더 효과적 인덱스 선택 가능


alter session set "_complex_view_merging"= false;


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)



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;



UNION 집합 연산자를 포함한 뷰에 대한 조건절 Pushdown


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



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



(2) 조건절 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           | 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)


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



(3) 조인 조건 Pushdown


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


  • 조인 조건 Pushdown 제어 힌트
    • push_pred
    • no_push_pred

  • 제어 파라미터
    • _push_join_predicate
    • _push_join_union_view
    • _push_join_union_view2


Group by 절을 포함한 뷰에 대한 조인 조건 Pushdown


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;

10g 이하 실행계획
-----------------------------------------------------------------------------
| 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")

11g 실행계획
------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                |    14 |   378 |     7  (15)| 00:00:01 |
|   1 |  HASH GROUP BY                |                |    14 |   378 |     7  (15)| 00:00:01 |
|   2 |   NESTED LOOPS OUTER          |                |    14 |   378 |     6   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL          | DEPT           |     4 |    80 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS BY INDEX ROWID| EMP            |     4 |    28 |     1   (0)| 00:00:01 |
|*  5 |     INDEX RANGE SCAN          | EMP_IDX_DEPTNO |     5 |       |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("DEPTNO"(+)="D"."DEPTNO")


☞ Group by 절을 포함한 뷰에 대한 조인 조건 Pushdown 11g 이후 제공 시작



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



Outer 조인 뷰에 대한 조인 조건 Pushdown


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




  • 오라클 고도화 원리와 해법 2 (bysql.net 2011년 1차 스터디)
  • 작성자: 위충환 (실천하자)
  • 최초작성일: 2011년 05월 30 일
  • 본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net
  • 문서의 잘못된 점이나 질문사항은 본 문서에 댓글로 남겨주세요. ^^