4. 조건절 Pushing

2011.05.31 12:11

실천하자 조회 수:7114

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	2. 서브쿼리 Unnesting	darkbeom	2011.05.15	19841
39	2. 소트를 발생시키는 오퍼레이션	휘휘	2011.06.12	3508
38	2. 파티션 Pruning	실천하자	2011.06.21	26102
37	3. 병렬 조인	실천하자	2011.06.28	7220
36	3. 다양한 인덱스 스캔 방식	멋진넘	2011.02.18	33881
35	3. 해시 조인	darkbeom	2011.03.20	21602
34	3. 옵티마이저의 한계	멋진넘	2011.04.19	7902
33	3. 뷰 Merging	실천하자	2011.05.15	23516
32	3. 데이터 모델 측면에서의 검토	멋진넘	2011.06.13	5922
31	3. 인덱스 파티셔닝	darkbeom	2011.06.19	54156
30	4. 테이블 Random 액세스 부하 [1]	darkbeom	2011.02.23	14754
29	4. 조인 순서의 중요성	운영자	2011.03.27	14374
28	4. 통계정보 Ⅰ	darkbeom	2011.04.25	18214
»	4. 조건절 Pushing	실천하자	2011.05.31	7114
26	4. 소트가 발생하지 않도록 SQL 작성	멋진넘	2011.06.12	7940
25	5. 테이블 Random 액세스 최소화 튜닝	suspace	2011.02.24	7028
24	6. 스칼라 서브쿼리를 이용한 조인 [1]	휘휘	2011.03.28	6686
23	5. Outer 조인	실천하자	2011.03.29	5084
22	5. 카디널리티	suspace	2011.04.26	5989
21	5. 조건절 이행	휘휘	2011.05.29	5509

첫 페이지 1 2 3 끝 페이지

쓰기

태그

bysql.net

로그인

4. 조건절 Pushing

댓글 0