Query Transformation - Filter Push Down

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 를 유도하는 것이 정답인 것이다.