FPD(Filter Push Down)이란 뷰 바깥쪽의 조건을 뷰안으로 진입 시킨다는 의미이다.
FPD 는 자체로도 작동하지만 Subquery Flattening, View Merging, Join Predicate Pushdown 등이 진행될 때에도 부가적으로 실행되는 아주 중요한  Query Transformation 기법 이다.
FPD 와 관련하여 튜너및 DBA 들이 잘못알고 있는 사실이 있으므로 이것을 바로 잡고자 한다.
no_merge 힌트를 사용하면 뷰 바깥쪽의 조건들이 뷰 내로 파고들지 못한다는 미신이 있다.
과연 이것이 맞는 이야기인가?
먼저 테스트를 위하여 테이블을 하나 만들어야 한다.
환경 Oracle 11.1.0.6

CREATE TABLE scott.BIG_EMP AS    --> big_emp 생성
SELECT ROWNUM AS EMPNO, A.ENAME, A.JOB, A.MGR, A.HIREDATE, A.SAL + rownum as SAL, A.COMM, A.DEPTNO
  FROM scott.EMP A,
       (SELECT LEVEL AS NO FROM DUAL CONNECT BY LEVEL <= 2000) B;

ALTER TABLE scott.BIG_EMP --> PK 생성
   ADD ( CONSTRAINT PK_BIG_EMP PRIMARY KEY (EMPNO) USING INDEX );

CREATE INDEX scott.BIG_EMP_SAL_IX ON scott.BIG_EMP (SAL); --> SAL 항목에 인덱스 생성

dbms_stats.gather_table_stats('SCOTT', 'BIG_EMP', cascade => true);


 아래의 SQL 을 본다면 no_merge 힌트는 Filter Push Down(조건절을 뷰안으로 진입)을 방해하지 않음을 알수 있다.

select /*+ gather_plan_statistics */
         deptno, max_sal
  from (select /*+ no_merge index(big_emp big_emp_sal_ix) */
                   deptno, max(SAL) max_sal
          from big_emp
         group by deptno)
where  max_sal  >  32900
;

--------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Starts | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   1 |  VIEW                          |                |      1 |      1 |00:00:00.01 |       3 |
|*  2 |   FILTER                       |                |      1 |      1 |00:00:00.01 |       3 |
|   3 |    HASH GROUP BY               |                |      1 |      1 |00:00:00.01 |       3 |
|   4 |     TABLE ACCESS BY INDEX ROWID| BIG_EMP        |      1 |      7 |00:00:00.01 |       3 |
|*  5 |      INDEX RANGE SCAN          | BIG_EMP_SAL_IX |      1 |      7 |00:00:00.01 |       2 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(MAX("SAL")>32900)
   5 - access("SAL">32900)    

재미있는 점은 뷰 바깥의 max_sal  >  32900 조건을 뷰내로 진입시키면서 아래처럼 where 절을 2단계로 추가 하였다.

1단계: having 절로 변환       --> having max(SAL) > 32900
2단계 :where 조건으로 변환  --> where sal > 32900

실제로 10053 Event 정보를 분석해보면 FPD(Filter Push Down) 가 2단계로 진행된다는 것을 알수 있다.
그러면 옵티마이져가 변경한 SQL 보자.

select /*+ gather_plan_statistics */
         deptno, max_sal
  from (select /*+ index(big_emp BIG_EMP_SAL_IX) */
                   deptno, max(sal) max_sal
          from big_emp
         where sal > 32900               --> 옵티마이져가 FPD 로 조건을 생성함.
         group by deptno
         having max(sal) >  32900    --> 옵티마이져가 FPD 로 조건을 생성함.
       )
;

--------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Starts | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   1 |  VIEW                          |                |      1 |      1 |00:00:00.01 |       3 |
|*  2 |   FILTER                       |                |      1 |      1 |00:00:00.01 |       3 |
|   3 |    HASH GROUP BY               |                |      1 |      1 |00:00:00.01 |       3 |
|   4 |     TABLE ACCESS BY INDEX ROWID| BIG_EMP        |      1 |      7 |00:00:00.01 |       3 |
|*  5 |      INDEX RANGE SCAN          | BIG_EMP_SAL_IX |      1 |      7 |00:00:00.01 |       2 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(MAX("SAL")>32900)
   5 - access("SAL">32900)

Plan 및 Predicate Information 가 원본쿼리와 완전히 일치함을 알수 있다.

오히려 이런 경우는 원본쿼리에 merge 힌트를 사용하는것이 더 위험하다.
아래의 SQL 을 보자.

select /*+ gather_plan_statistics qb_name(outer) */
          deptno, max_sal
  from (select /*+ MERGE qb_name(inner) index(big_emp BIG_EMP_SAL_IX) */
                   deptno, max(sal) max_sal
          from big_emp
         group by deptno
         )
where  max_sal  >  32900
;


--------------------------------------------------------------------------------
| Id  | Operation           | Name    | Starts | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------
|*  1 |  FILTER             |         |      1 |      1 |00:00:00.02 |     177 |
|   2 |   HASH GROUP BY     |         |      1 |      3 |00:00:00.02 |     177 |
|   3 |    TABLE ACCESS FULL| BIG_EMP |      1 |  28000 |00:00:00.03 |     177 |
--------------------------------------------------------------------------------

 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(MAX("SAL")>32900)

PLAN 정보의 Buffers(read 한 블럭수) 를 보면 일량이 3에서 177 로 늘어난 것을 알수 있다.
Group By 된 뷰가 merge 힌트에 의해서 해체 되었다.
Merge 되는 경우는 having 절만 생성되며 where 조건은 추가적으로 생성되지 않음을 알수 있다.
따라서 다음과 같은 SQL 로 변환된것을 알수 있다.

select deptno,
       max(sal) max_sal
  from big_emp
 group by deptno
 having max(sal) >  32900 ;  --> 추가적인 where 절이 없음

위와 같이 변경된것을 Complex View Merging 이라고 한다.
Complex 가 추가적으로 붙은 이유는 group by 절이 있는 뷰를 Merge(해체) 했기 때문이다.

결론 :
집계함수를 사용하는 뷰의 바깥에서 집계함수의 결과를 filter 할경우 merge 힌트를 사용하면 뷰가 해체 되면서 Having 절로 변환이 되지만 where 조건은 생성되지 않으므로 인덱스가 있다고 해도 사용할수 없음을 알수 있다.
오히려 이런 경우는 no_merge 힌트를 사용하여 FPD 를 유도하는 것이 정답인 것이다.

Posted by extremedb

댓글을 달아 주세요

  1. feelie 2009.09.23 21:36  댓글주소  수정/삭제  댓글쓰기

    지난내용이데 궁금해서 질문합니다.
    Subquery Flattening - SEMI join,
    View Merging - 뷰쿼리가 액세스 쿼리로 변환
    Join Predicate Pushdown - 액세스쿼리가 뷰쿼리로 변환
    맞습니까?
    filter push down 도 조건절진입과 동일한 것인가요?
    모 책에서는 조건절진입, 뷰 병합 으로만 설명이 되어있어 위의 내용들에 대한 정확한 개념이 잡히질 않네요..

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2009.09.23 22:42 신고  댓글주소  수정/삭제

      말씀하신 책을 읽은지가 오래되서 가물가물 합니다.
      그래서 다른방법으로 설명드립니다.
      Subquery Flattening - (SEMI/ANTI join, Subquery Unnesting) -서브쿼리가 없어지고 조인으로 바뀜.
      View Merging - 뷰 혹은 인라인뷰가 해체 됨(테이블끼리 조인으로 바뀜)
      JPPD - 조인조건이 뷰 혹은 인라인뷰 내부로 파고 듬
      FILTER PUSH DOWN - 조인조건이 아닌 FILTER 조건이 뷰 혹은 인라인뷰 내부로 파고듬.
      이렇게 정리하시면 쉬울겁니다.

      외국의 유명한 영문학 교수가 한국에 와서 영어문법에 대한 강연을 하는데 한 학생이 질문을 하였습니다.
      학생 :'5형식중에서 가장 쉬운것을 하나 소개해주세요'
      교수의 답변이 쇼킹 합니다.
      교수 : '5형식이 뭔가요?'

      뷰쿼리, 엑세스쿼리 ....음 용어 정말 어렵네요.
      하지만 일반적으로 통용되는 용어라면 아시는게 좋을수도....

  2. feelie 2009.09.24 12:59  댓글주소  수정/삭제  댓글쓰기

    감사합니다.
    JPPD, FPD을 구별하기가 어려웠는데 조인조건이냐 아니냐 군요.
    감사합니다.

  3. Favicon of https://1ststreet.tistory.com BlogIcon SITD 2011.05.12 15:14 신고  댓글주소  수정/삭제  댓글쓰기

    옵티마이져 진짜 영리하네요.

    좋은 글 감사드립니다~

  4. eqon 2013.11.20 11:34  댓글주소  수정/삭제  댓글쓰기

    오라클 버전은 11.2.0.2.0

    view내부로 predicate파고 들어가지 않게 제어 하고 싶은데 계속 들어가버리네요.

    53 트레이스에서 확인해보니까 아래와 같이 FPD가 작동하네요.

    FPD: Considering simple filter push (pre rewrite) in query block QB2 (#0)

    FPD기능을 끌 수 있는 파라미터가 없을까요

    /*+ NO_MERGE no_push_pred
    OPT_PARAM('_push_join_predicate' 'false')
    OPT_PARAM('_pred_move_around' 'false')
    OPT_PARAM('_optimizer_push_pred_cost_based' 'false')
    OPT_PARAM('_simple_view_merging' 'false')
    OPT_PARAM('_optimizer_enhanced_filter_push' 'false' )
    OPT_PARAM('_optimizer_filter_pred_pullup' 'false') */

    이 정도 힌트 넣어봤는데 아무것도 안 먹네요.
    선생님 책 93페이지 filter push down절에서도 제어할 수 있는 파라미터는 안 나와 있네요. 할 수 없이 뷰 안에 의미없는
    rownum > 0 넣어주니까 기능이 작동하지 않는 걸 확인했는데요.
    힌트를 제어할 수 있는 방법을 알고 싶어요.