by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

4. 조건절 Pushing




조건절 Pushing

  • 옵티마이져가 뷰를 액세스하는 쿼리를 최적화하는 단계
    • View Merging
      ? 쿼리내부의 인라인뷰/뷰를 풀어 메인쿼리와 같은 레벨의 쿼리블록으로 만드는 쿼리 변환기능이다. (작은 단위의 쿼리블록보다 Merging/Unest된 경우 쿼리 최적화를 위한 다양한 cost계산을 할 수 있기 때문)
    • Push Predicate
      ? 해당 뷰를 참조하는 쿼리블록의 조건절을 뷰안의 쿼리로 제공(push)하는 기능임
  • 뷰머징이 실패할 경우
    • 복합 뷰(Complex View) Merging기능이 비활성화
    • 사용자가 No_merge힌트를 사용한 경우
    • 뷰안에 Rownum Psedo컬럼이 있는 경우(조건절 Pushing도 되지 않음.)
    • 분석함수를 사용한 경우(조건절 Pushing도 되지 않음.)
    • 비용기반 쿼리 변환이 작동해 No Merging을 선택한 경우
    • Non-mergeable Views : 뷰 Merging이 시행되면 부정확한 결과가 나올 경우?
  • 조건절 Pushing의 종류
    • 조건절(Predicate) Pushdown
      ? 쿼리블록 밖에 있는 조건들을 쿼리 블록 안쪽으로 밀어 넣는 것을 말함
    • 조건절(Predicate) Pullup
      ? 쿼리 블록 안에 있는 조건들을 쿼리 블록 밖으로 내와서 다른 쿼리블록에 Pushdown하는데 사용( Predicate Move Around)
    • 조인조건(Join Predicate) Pushdown
      ? NL조인 수행 중에 드라이빙테이블에서 읽은 값을 건건이 Inner쪽으로 밀어 넣는 것을 말함

관련 힌트와 파라미터 :

    • /*+ push_pred(table_name/alias) */
    • /*+ no_push_pred(table_name/alias) */
    • /*+ opt_param('_optimizer_push_pred_cost_based', 'false') */
    • /*+ opt_param('_push_join_predicate', 'false') */
    • /*+ opt_param('_push_join_union_view', 'false') */
    • /*+ opt_param('_push_join_union_view2', 'false') */
      Be Careful
      • 조건절Pushdown/Pullup은 항상 더 나은 성능을 보장함
      • 조인조건Pushdown은 NL조인을 전제로함, NL의 특성상 성능저하될 수 있어 제어힌트 제공
      • 조인조건Pushdown은 NL조인을 전제로 하기 때문에 굳이 use_nl힌트를 줄 필요는 없다.
      • 9i에서는 push_pred와 use_nl힌트를 함께 사용할 때 pushdown기능이 작동하지 않을 수 있다.

조건절 Pushdown

Group By 절을 포함한 뷰에 대한 Pushdown

================================================================
primary key : dept_pk on dept(deptno);
index       : emp_deptno_idx on emp(deptno);

?	단순한 Group By 뷰에 조건절이 파고든 예
?	뷰 내부에 조건절이 없었지만 Predicate정보에 보면 access("DEPTNO"=30)로 조건이 뷰 안에서 실행된 것을 확인할 수 있다.
orcl:WOONG >
  1  select deptno, avg_sal
  2  from  (select deptno, avg(sal) avg_sal from emp group by deptno) a
  3  where  deptno = 30;
경   과: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 1032861127

----------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     1 |     7 |
|   1 |  SORT GROUP BY NOSORT        |                |     1 |     7 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP            |     5 |    35 |
|*  3 |    INDEX RANGE SCAN          | EMP_DEPTNO_IDX |     5 |       |
----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("DEPTNO"=30)
Group By절이 포함되어 있는 뷰에 대한 조인을 했을 경우의 예
뷰 merging이 되지 않도록 no_merge힌트로 제어한 후 테스트함
단순한 Group By 뷰에 파고든 예와 같이 뷰안에서 조건이 수행된 것을 확인할 수 있다.

orcl:WOONG >
  1  select /*+ no_merge(a) */
  2         b.deptno, b.dname, a.avg_sal
  3  from  (select deptno, avg(sal) avg_sal from emp group by deptno) a
  4       , dept b
  5  where  a.deptno = b.deptno
  6  and    b.deptno = 30;
경   과: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 1855526360

-------------------------------------------------------------------------
| Id  | Operation                      | Name           | Rows  | Bytes |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                |     1 |    28 |
|   1 |  NESTED LOOPS                  |                |     1 |    28 |
|   2 |   TABLE ACCESS BY INDEX ROWID  | DEPT           |     1 |    13 |
|*  3 |    INDEX UNIQUE SCAN           | DEPT_PK        |     1 |       |
|   4 |   VIEW                         |                |     1 |    15 |
|   5 |    SORT GROUP BY               |                |     1 |    10 |
|   6 |     TABLE ACCESS BY INDEX ROWID| EMP            |     5 |    50 |
|*  7 |      INDEX RANGE SCAN          | EMP_DEPTNO_IDX |     5 |       |
-------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("B"."DEPTNO"=30)
   7 - access("DEPTNO"=30)


실제로는 아래처럼 '조건절 이행'이 먼저 일어났다.
조인조건에 의해서 내부적으로 조건절이 추가되어 집합A로 파고들 수 있었기 때문이다. 

orcl:WOONG >
1  select /*+ no_merge(a) */
  2         b.deptno, b.dname, a.avg_sal
  3  from  (select deptno, avg(sal) avg_sal from emp group by deptno) a
  4       , dept b
  5  where  a.deptno = b.deptno
  6  and    b.deptno = 30
  7  and    a.deptno = 30;
경   과: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 1855526360

-------------------------------------------------------------------------
| Id  | Operation                      | Name           | Rows  | Bytes |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                |     1 |    39 |
|   1 |  NESTED LOOPS                  |                |     1 |    39 |
|   2 |   TABLE ACCESS BY INDEX ROWID  | DEPT           |     1 |    13 |
|*  3 |    INDEX UNIQUE SCAN           | DEPT_PK        |     1 |       |
|   4 |   VIEW                         |                |     1 |    26 |
|   5 |    SORT GROUP BY               |                |     1 |    10 |
|   6 |     TABLE ACCESS BY INDEX ROWID| EMP            |     5 |    50 |
|*  7 |      INDEX RANGE SCAN          | EMP_DEPTNO_IDX |     5 |       |
-------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("B"."DEPTNO"=30)
   7 - access("DEPTNO"=30)


결국 조인컬럼이 가공되어 조건절 이행이 일어날 수 없다면 Pushdown은 일어날 수 없다.


orcl:WOONG >
  1  select /*+ no_merge(a) */
  2         b.deptno, b.dname, a.avg_sal
  3  from  (select deptno||'' deptno
  4              , avg(sal) avg_sal from emp group by deptno) a
  5       , dept b
  6  where  a.deptno = b.deptno
  7  and    b.deptno = 30;
경   과: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 4150857296

----------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes |
----------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |    53 |
|   1 |  NESTED LOOPS                |         |     1 |    53 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    16 |
|*  3 |    INDEX UNIQUE SCAN         | DEPT_PK |     1 |       |
|   4 |   VIEW                       |         |     1 |    37 |
|   5 |    SORT GROUP BY             |         |     1 |     7 |
|*  6 |     TABLE ACCESS FULL        | EMP     |     1 |     7 |
----------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("B"."DEPTNO"=30)
   6 - filter(TO_NUMBER(TO_CHAR("DEPTNO")||'')=30)

================================================================
Union 집합연산자를 포함한 Pushdown
================================================================
primary key : dept_pk on dept(deptno);
index :       emp_x1 on emp(deptno, job);

Union/Minus같은 집합연산자를 포함하는 뷰는 뷰 Merging이 되지 않으므로 Pushdown으로만 최적화 될 수 있다.
뷰 merging이 되지 않도록 no_merge힌트로 제어한 후 테스트함
뷰내부의 조건절은 job만 존재하였으나 메인쿼리의 조건(v.deptno = 30)이 파고들어 뷰 안에서 조건에 따른 결합인덱스(emp_x1)를 이용한 처리를 한 것을 확인할 수 있다.

orcl:WOONG >
  1  select *
  2  from  (select deptno, empno, ename, job, sal, sal * 1.1 sal2, hiredate
  3         from emp
  4         where  job = 'CLERK'
  5         union all
  6         select deptno, empno, ename, job, sal, sal * 1.2 sal2, hiredate
  7         from emp
  8         where  job = 'SALESMAN' ) v
  9  where  v.deptno = 30;
경   과: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 3488565791

---------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes |
---------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |     2 |   148 |
|   1 |  VIEW                         |        |     2 |   148 |
|   2 |   UNION-ALL                   |        |       |       |
|   3 |    TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    33 |
|*  4 |     INDEX RANGE SCAN          | EMP_X1 |     2 |       |
|   5 |    TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    33 |
|*  6 |     INDEX RANGE SCAN          | EMP_X1 |     2 |       |
---------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("DEPTNO"=30 AND "JOB"='CLERK')
   6 - access("DEPTNO"=30 AND "JOB"='SALESMAN')


Union/Minus같은 집합연산자를 포함하는 뷰는 뷰 Merging이 되지 않으므로 Pushdown으로만 최적화 될 수 있다.
뷰 merging이 되지 않도록 no_merge힌트로 제어한 후 테스트함
뷰내부의 조건절은 job만 존재하였으나 메인쿼리의 조건(v.deptno = 30)이 파고들어 뷰 안에서 조건에 따른 결합인덱스(emp_x1)를 이용한 처리를 한 것을 확인할 수 있다.


orcl:WOONG >
  1  select /*+ ordered use_nl(e) */ d.dname, e.*
  2  from   dept d
  3       ,(select deptno, empno, ename, job, sal, sal * 1.1 sal2, hiredate from emp
  4         where  job = 'CLERK'
  5         union all
  6         select deptno, empno, ename, job, sal, sal * 1.2 sal2, hiredate from emp
  7         where  job = 'SALESMAN' ) e
  8  where  e.deptno = d.deptno
  9  and    d.deptno = 30;
경   과: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 1945841114

-----------------------------------------------------------------
| Id  | Operation                      | Name    | Rows  | Bytes |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT               |         |     2 |   174 |
|   1 |  NESTED LOOPS                  |         |     2 |   174 |
|   2 |   TABLE ACCESS BY INDEX ROWID  | DEPT    |     1 |    13 |
|*  3 |    INDEX UNIQUE SCAN           | DEPT_PK |     1 |       |
|   4 |   VIEW                         |         |     2 |   148 |
|   5 |    UNION-ALL                   |         |       |       |
|   6 |     TABLE ACCESS BY INDEX ROWID| EMP     |     1 |    33 |
|*  7 |      INDEX RANGE SCAN          | EMP_X1  |     2 |       |
|   8 |     TABLE ACCESS BY INDEX ROWID| EMP     |     1 |    33 |
|*  9 |      INDEX RANGE SCAN          | EMP_X1  |     2 |       |
-----------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("D"."DEPTNO"=30)
   7 - access("DEPTNO"=30 AND "JOB"='CLERK')
   9 - access("DEPTNO"=30 AND "JOB"='SALESMAN')

================================================================
조건절 Pullup
================================================================
primary key : dept_pk on dept(deptno);
index       : emp_deptno_idx on emp(deptno);

orcl:WOONG >
  1  select * from
  2    (select deptno, avg(sal) from emp where deptno = 10 group by deptno) e1
  3   ,(select deptno, min(sal), max(sal) from emp group by deptno) e2
  4  where  e1.deptno = e2.deptno ;
경   과: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 1076936357

------------------------------------------------------------------------
| Id  | Operation                      | Name           | Rows  | Bytes |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                |     1 |    69 |
|*  1 |  HASH JOIN                     |                |     1 |    69 |
|   2 |   VIEW                         |                |     1 |    28 |
|   3 |    HASH GROUP BY               |                |     1 |    10 |
|   4 |     TABLE ACCESS BY INDEX ROWID| EMP            |     5 |    50 |
|*  5 |      INDEX RANGE SCAN          | EMP_DEPTNO_IDX |     5 |       |
|   6 |   VIEW                         |                |     3 |   123 |
|   7 |    HASH GROUP BY               |                |     3 |    30 |
|   8 |     TABLE ACCESS BY INDEX ROWID| EMP            |     5 |    50 |
|*  9 |      INDEX RANGE SCAN          | EMP_DEPTNO_IDX |     5 |       |
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("E1"."DEPTNO"="E2"."DEPTNO")
   5 - access("DEPTNO"=10)
   9 - access("DEPTNO"=10)


Predicate Information을 보면 두개의 인라인뷰 중에 where 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 ;


================================================================
조인조건 Pushdown
인라인뷰 e에는 d.deptno에 대한 참조가 없음에도 실행계획에는 조인조건이 들어간 것과 같은 Pushdown효과를 보이고 있다.
실행계획에 VIEW PUSHED PREDICATE 오퍼레이션을 확인할 수 있다.

orcl:WOONG >
  1  select /*+ no_merge(e) push_pred(e) */ *
  2  from  dept d, (select empno, ename, deptno from emp) e
  3  where e.deptno(+) = d.deptno
  4  and   d.loc = 'CHICAGO';
경   과: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 3116586712

------------------------------------------------------------------------
| Id  | Operation                     | Name           | Rows  | Bytes |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                |     4 |   220 |
|   1 |  NESTED LOOPS OUTER           |                |     4 |   220 |
|*  2 |   TABLE ACCESS FULL           | DEPT           |     1 |    20 |
|   3 |   VIEW PUSHED PREDICATE       |                |     1 |    35 |
|   4 |    TABLE ACCESS BY INDEX ROWID| EMP            |     5 |    80 |
|*  5 |     INDEX RANGE SCAN          | EMP_DEPTNO_IDX |     5 |       |
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("D"."LOC"='CHICAGO')
   5 - access("DEPTNO"="D"."DEPTNO")

Group By 절을 포함한 뷰에 대한 Pushdown
===============================================================
10g이전에는 Group By를 포함하는 뷰에 대한 조인조건 pushdown기능이 지원되지 않는다.(?)
11g부터 가능함
아래 예제처럼 Outer관계라면 스칼라쿼리로 변환할 수 있으며 이때 여러 컬럼을 참조한다면 Type오브젝트를 사용하거나 Substr함수로 잘라서 사용할 수 있다.

orcl:WOONG >
  1  select /*+ leading(d) use_nl(e) no_merge(e) push_pred(e) index(e (deptno)) */
  2         d.deptno, d.dname, e.avg_sal
  3  from   dept d
  4       , (select deptno, avg(sal) avg_sal from emp group by deptno) e
  5  where  e.deptno = d.deptno;
경   과: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 1993021132

----------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes |
----------------------------------------------------
|   0 | SELECT STATEMENT     |      |     3 |   117 |
|   1 |  NESTED LOOPS        |      |     3 |   117 |
|   2 |   TABLE ACCESS FULL  | DEPT |     4 |    52 |
|*  3 |   VIEW               |      |     1 |    26 |
|   4 |    SORT GROUP BY     |      |     3 |    21 |
|   5 |     TABLE ACCESS FULL| EMP  |    14 |    98 |
----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("E"."DEPTNO"="D"."DEPTNO")
===============================================================

Union 집합연산자를 포함한 Pushdown
===============================================================
뷰내부의 조건절은 job만 존재하였으나 메인쿼리의 조건(d.loc = 'CHICAGO')이 파고들어 뷰 안에서 조건에 따른 결합인덱스(emp_x1)를 이용한 처리를 한 것을 확인할 수 있다. 

orcl:WOONG >
  1  select /*+ push_pred(e) */ d.dname, e.*
  2  from   dept d
  3       ,(select deptno, empno, ename, job, sal, sal * 1.1 sal2, hiredate from emp
  4         where  job = 'CLERK'
  5         union all
  6         select deptno, empno, ename, job, sal, sal * 1.2 sal2, hiredate from emp
  7         where  job = 'SALESMAN' ) e
  8  where  e.deptno = d.deptno
  9  and    d.loc = 'CHICAGO';
경   과: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 4023361524

------------------------------------------------------------------
| Id  | Operation                      | Name     | Rows  | Bytes |
------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |     2 |   200 |
|   1 |  NESTED LOOPS                  |          |     2 |   200 |
|   2 |   TABLE ACCESS BY INDEX ROWID  | DEPT     |     1 |    20 |
|*  3 |    INDEX RANGE SCAN            | DEPT_IDX |     1 |       |
|   4 |   VIEW                         |          |     1 |    80 |
|   5 |    UNION ALL PUSHED PREDICATE  |          |       |       |
|   6 |     TABLE ACCESS BY INDEX ROWID| EMP      |     1 |    36 |
|*  7 |      INDEX RANGE SCAN          | EMP_IDX  |     2 |       |
|   8 |     TABLE ACCESS BY INDEX ROWID| EMP      |     1 |    36 |
|*  9 |      INDEX RANGE SCAN          | EMP_IDX  |     2 |       |
------------------------------------------------------------------

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
  • Outer 조인에서 Inner쪽 집합이 뷰 쿼리일 때 테이블개수에 따라 옵티마이져는 2가지 방법 중 하나를 선택한다.
  1. 뷰 안에서 참조하는 테이블이 단 하나일 때, 뷰 머징을 시도한다.
  2. 뷰 안에서 참조하는 테이블이 2개 이상일 때 조인조건식을 Pushdown하려 한다.
  3. 뷰 내에서 참조하는 테이블 중 하나라도 no_merge로 뷰머징을 제한하면 Pushdown하려 한다.
===============================================================
orcl:WOONG >
  1  select /*+ push_pred(b) */
  2         a.empno, a.ename, a.sal, a.hiredate, b.deptno, b.dname, b.loc, a.job
  3  from   emp a
  4       ,(select e.empno, d.deptno, d.dname, d.loc
  5         from   emp e, dept d
  6         where  d.deptno = e.deptno
  7         and    e.sal >= 1000
  8         and    d.loc in ( 'CHICAGO', 'NEW YORK' ) ) b
  9  where  b.empno(+) = a.empno
 10  and    a.hiredate >= to_date('19810901', 'yyyymmdd');
경   과: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 3468508478

--------------------------------------------------------------------------
| Id  | Operation                      | Name             | Rows  | Bytes |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                  |    14 |   952 |
|   1 |  NESTED LOOPS OUTER            |                  |    14 |   952 |
|   2 |   TABLE ACCESS BY INDEX ROWID  | EMP              |    14 |   476 |
|*  3 |    INDEX RANGE SCAN            | EMP_HIREDATE_IDX |    14 |       |
|   4 |   VIEW PUSHED PREDICATE        |                  |     1 |    34 |
|   5 |    NESTED LOOPS                |                  |     1 |    35 |
|*  6 |     TABLE ACCESS BY INDEX ROWID| EMP              |     1 |    15 |
|*  7 |      INDEX UNIQUE SCAN         | EMP_PK           |     1 |       |
|*  8 |     TABLE ACCESS BY INDEX ROWID| DEPT             |     2 |    40 |
|*  9 |      INDEX UNIQUE SCAN         | DEPT_PK          |     1 |       |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("A"."HIREDATE">=TO_DATE('1981-09-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
   6 - filter("E"."SAL">=1000)
   7 - access("E"."EMPNO"="A"."EMPNO")
   8 - filter("D"."LOC"='CHICAGO' OR "D"."LOC"='NEW YORK')
   9 - access("D"."DEPTNO"="E"."DEPTNO")
===============================================================

문서에 대하여

  • 최초작성자 : [이지웅]
  • 최초작성일 : 2010년 05월 14일
  • 이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
  • 이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법II'를 참고하였습니다.

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.
  1. 5월 14, 2010

    이지웅 says:

    아~ wiki포맷팅할라니 힘들다 ㅜ_ㅜ

    아~ wiki포맷팅할라니 힘들다 ㅜ_ㅜ

    1. 5월 14, 2010

      임주영 says:

      그래도 잘 하셨는데요.. ^^

      그래도 잘 하셨는데요.. ^^