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

이 게시물을

번호	제목	글쓴이	날짜	조회 수
60	진행기록	운영자	2011.08.22	3378
59	1. 기본 개념	멋진넘	2011.06.28	13517
58	1. 테이블 파티셔닝	suspace	2011.06.21	6873
57	7장. 병렬 처리	운영자	2011.06.14	4660
56	6장. 파티셔닝	운영자	2011.06.14	3222
55	5장. 소트 튜닝	운영자	2011.06.14	3338
54	4장. 쿼리 변환	운영자	2011.06.14	3171
53	3장. 옵티마이저 원리	운영자	2011.06.14	3194
52	1. 소트 수행 원리	휘휘	2011.06.12	5811
51	1. 쿼리 변환이란?	실천하자	2011.05.01	4984
50	1. 옵티마이저	실천하자	2011.04.17	11282
49	1. Nested Loops 조인	suspace	2011.03.22	8352
48	2장. 조인 원리와 활용	운영자	2011.03.22	3361
47	1. 인덱스 구조 [1]	실천하자	2011.02.16	14277
46	1장. 인덱스 원리와 활용	운영자	2011.06.14	5663
45	온라인 OT 및 스터디 툴 사용 방법	휘휘	2011.02.16	3381
44	Front Page	운영자	2011.02.15	149472
43	2. 인덱스 기본 원리	실천하자	2011.02.20	3413
42	2. 소트 머지 조인	실천하자	2011.03.22	5864
41	2. 옵티마이저 행동에 영향을 미치는 요소	휘휘	2011.04.17	5159

첫 페이지 1 2 3 끝 페이지

쓰기

태그

bysql.net

로그인

4. 조건절 Pushing

댓글 0