메뉴 건너뛰기

bysql.net

4. 조건절 Pushing

2011.05.31 21:11

실천하자 조회 수:7021

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
  • 문서의 잘못된 점이나 질문사항은 본 문서에 댓글로 남겨주세요. ^^


이 게시물을
목록
목록
번호 제목 글쓴이 날짜 조회 수
40 10. 실체화 뷰 쿼리로 재작성 suspace 2011.06.07 5531
39 9. Outer 조인을 Inner 조인으로 변환 darkbeom 2011.06.07 7834
38 8. 공통 표현식 제거 darkbeom 2011.06.07 5223
37 12. 기타 쿼리 변환 [3] 실천하자 2011.06.03 6646
» 4. 조건절 Pushing 실천하자 2011.05.31 7021
35 7. OR-Expansion 멋진넘 2011.05.31 8720
34 6. 조인 제거 멋진넘 2011.05.30 4595
33 5. 조건절 이행 휘휘 2011.05.30 5406
32 2. 서브쿼리 Unnesting darkbeom 2011.05.16 19700
31 3. 뷰 Merging 실천하자 2011.05.15 23381
30 7. 비용 휘휘 2011.05.03 6167
29 1. 쿼리 변환이란? 실천하자 2011.05.02 4927
28 8. 통계정보 Ⅱ [1] 멋진넘 2011.04.30 31079
27 5. 카디널리티 suspace 2011.04.26 5953
26 4. 통계정보 Ⅰ darkbeom 2011.04.26 18091
25 6. 히스토그램 실천하자 2011.04.25 10917
24 3. 옵티마이저의 한계 멋진넘 2011.04.19 7857
23 1. 옵티마이저 file 실천하자 2011.04.18 11211
22 2. 옵티마이저 행동에 영향을 미치는 요소 휘휘 2011.04.18 5094
21 8. 고급 조인 테크닉-2 file suspace 2011.04.05 7017
쓰기
태그