메뉴 건너뛰기

bysql.net

2.8_FPD_(Filter_Push_Down)

2011.09.18 06:46

darkbeom 조회 수:2365

2.2.8 FPD - 조건절을 뷰 내부로 이동시켜라.


(참고 : 오라클성능고도화2권 서브쿼리 Unnesting, 뷰 Merging, 조건절 Pushing )


-- 뷰 혹은 인라인뷰 바깥에서 뷰 내부의 컬럼들을 이용하여 조건절을 사용할때 조건절이 뷰 내부로 침투되는 현상


ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
 
SELECT A.EMPLOYEE_ID, A.FIRST_NAME,
       A.LAST_NAME, A.EMAIL,
       B.DEPARTMENT_NAME
  FROM (SELECT /*+ NO_MERGE */ -- ①
               EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, EMAIL,
               DEPARTMENT_ID
          FROM EMPLOYEE) A,
       DEPARTMENT B
 WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
   AND A.JOB_ID = 'MK_REP'; -- ②

ALTER SESSION SET EVENTS '10053 trace name context off';


----------------------------------------------------+-----------------------------------+
| Id  | Operation                       | Name      | Rows  | Bytes | Cost  | Time      |
----------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT                |           |       |       |     3 |           |
| 1   |  NESTED LOOPS                   |           |       |       |       |           |
| 2   |   NESTED LOOPS                  |           |     1 |    84 |     3 |  00:00:01 |
| 3   |    VIEW                         |           |     1 |    68 |     2 |  00:00:01 |
| 4   |     TABLE ACCESS BY INDEX ROWID | EMPLOYEE  |     1 |    39 |     2 |  00:00:01 |
| 5   |      INDEX RANGE SCAN           | EMP_JOB_IX|     1 |       |     1 |  00:00:01 | -- ②
| 6   |    INDEX UNIQUE SCAN            | DEPT_ID_PK|     1 |       |     0 |           |
| 7   |   TABLE ACCESS BY INDEX ROWID   | DEPARTMENT|     1 |    16 |     1 |  00:00:01 |
----------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
5 - access("JOB_ID"='MK_REP') -- ②
6 - access("A"."DEPARTMENT_ID"="B"."DEPARTMENT_ID")
 
Content of other_xml column
===========================
  db_version     : 11.2.0.1
  parse_schema   : TLO
  plan_hash      : 878568610
  plan_hash_2    : 3349597697
  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$1")
      NO_ACCESS(@"SEL$1" "A"@"SEL$1")
      INDEX(@"SEL$1" "B"@"SEL$1" ("DEPARTMENT"."DEPARTMENT_ID"))
      LEADING(@"SEL$1" "A"@"SEL$1" "B"@"SEL$1")
      USE_NL(@"SEL$1" "B"@"SEL$1")
      NLJ_BATCHING(@"SEL$1" "B"@"SEL$1")
      INDEX_RS_ASC(@"SEL$2" "EMPLOYEE"@"SEL$2" ("EMPLOYEE"."JOB_ID"))
    END_OUTLINE_DATA
  */

-- ① /*+ NO_MERGE */ 힌트 : SVM(Simple View Merging)을 피하기 위함. SVM이 발생하면 뷰가 해체 되므로
      인라인뷰로 파고드는 Filter를 관찰할 수가 없기 때문이다.
-- ② A.JOB_ID = 'MK_REP' 조건이 인라인뷰로 파고듬으로서 EMP_JOB_IX 활용.

**************************
Predicate Move-Around (PM)
**************************
PM:   Passed validity checks.
PM:   Pushed down predicate "EMPLOYEE"."JOB_ID"='MK_REP'
 from query block SEL$1 (#0) to query block SEL$2 (#0)
PM:     PM bypassed: checking.
query block SEL$1 (#0) unchanged
FPD: Considering simple filter push in query block SEL$1 (#0)
"A"."DEPARTMENT_ID"="B"."DEPARTMENT_ID"
try to generate transitive predicate from check constraints for query block SEL$1 (#0)
finally: "A"."DEPARTMENT_ID"="B"."DEPARTMENT_ID"

JPPD:     JPPD bypassed: View not on right-side of outer-join.
kkqfppRelFilter: Not pushing filter predicates in query block SEL$2 (#0) because no predicate to push
FPD: Considering simple filter push in query block SEL$2 (#0)
"EMPLOYEE"."JOB_ID"='MK_REP'
try to generate transitive predicate from check constraints for query block SEL$2 (#0)
constraint: "EMPLOYEE"."LAST_NAME" IS NOT NULL

finally: "EMPLOYEE"."JOB_ID"='MK_REP' -- ②

apadrv-start sqlid=16164148079056840059
  :
    call(in-use=1796, alloc=16360), compile(in-use=64640, alloc=66288), execution(in-use=2504, alloc=4060)

-- 아래와 같이 쿼리변환이 이루어졌다.
-- FPD의 경우 특정한 Title이 없기 때문에 PM이 끝나고 PFD가 실행 된것이다.


EXPLAIN PLAN FOR
SELECT A.EMPLOYEE_ID, A.FIRST_NAME,
       A.LAST_NAME, A.EMAIL,
       B.DEPARTMENT_NAME
  FROM (SELECT /*+ NO_MERGE */
               EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, EMAIL,
               DEPARTMENT_ID
          FROM EMPLOYEE
         WHERE JOB_ID = 'MK_REP') A, -- ②
       DEPARTMENT B
 WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);


---------------------------------------------------------------------------------------------
| Id  | Operation                      | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |            |     1 |    82 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                  |            |       |       |            |          |
|   2 |   NESTED LOOPS                 |            |     1 |    82 |     3   (0)| 00:00:01 |
|   3 |    VIEW                        |            |     1 |    66 |     2   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| EMPLOYEE   |     1 |    39 |     2   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | EMP_JOB_IX |     1 |       |     1   (0)| 00:00:01 | -- ②
|*  6 |    INDEX UNIQUE SCAN           | DEPT_ID_PK |     1 |       |     0   (0)| 00:00:01 |
|   7 |   TABLE ACCESS BY INDEX ROWID  | DEPARTMENT |     1 |    16 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   5 - access("JOB_ID"='MK_REP') -- ②
   6 - access("A"."DEPARTMENT_ID"="B"."DEPARTMENT_ID")

-- Simple View : 위의 SQL과 같이 인라인뷰 내부에 Group By 나 Distinct를 사용하지 않았다.
■ 제약사항
-- Simple View라고 하더라도 뷰나 인라인뷰의 내부에서 Rownum, Rank등의 분석함수를 사용한다면 FPD의 기능을 이용할 수 없다.


EXPLAIN PLAN FOR
SELECT A.EMPLOYEE_ID, A.FIRST_NAME, A.LAST_NAME, A.EMAIL, B.DEPARTMENT_NAME,
       A.SALARY_RANK
  FROM (SELECT /*+ NO_MERGE */
               EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, EMAIL, DEPARTMENT_ID,
               RANK () OVER (ORDER BY SALARY) SALARY_RANK
          FROM EMPLOYEE) A,
       DEPARTMENT B
 WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
   AND A.JOB_ID = 'MK_REP';   -- ②
  
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);


--------------------------------------------------------------------------------------------
| Id  | Operation               | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                  |   104 | 10608 |     8  (25)| 00:00:01 |
|*  1 |  HASH JOIN              |                  |   104 | 10608 |     8  (25)| 00:00:01 |
|   2 |   VIEW                  | index$_join$_003 |    27 |   432 |     3  (34)| 00:00:01 |
|*  3 |    HASH JOIN            |                  |       |       |            |          |
|   4 |     INDEX FAST FULL SCAN| DEPT_ID_PK       |    27 |   432 |     1   (0)| 00:00:01 |
|   5 |     INDEX FAST FULL SCAN| DEPT_NAME_IDX    |    27 |   432 |     1   (0)| 00:00:01 |
|*  6 |   VIEW                  |                  |   107 |  9202 |     5  (20)| 00:00:01 | -- ②
|   7 |    WINDOW SORT          |                  |   107 |  5564 |     5  (20)| 00:00:01 |
|   8 |     TABLE ACCESS FULL   | EMPLOYEE         |   107 |  5564 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("A"."DEPARTMENT_ID"="B"."DEPARTMENT_ID")
   3 - access(ROWID=ROWID)
   6 - filter("A"."JOB_ID"='MK_REP') -- ②


EXPLAIN PLAN FOR
SELECT A.EMPLOYEE_ID, A.FIRST_NAME, A.LAST_NAME, A.EMAIL, B.DEPARTMENT_NAME,
       A.RN
  FROM (SELECT /*+ NO_MERGE */
               EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, EMAIL, DEPARTMENT_ID,
               ROWNUM RN
          FROM EMPLOYEE) A,
       DEPARTMENT B
 WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
   AND A.JOB_ID = 'MK_REP'; -- ②
  
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);


--------------------------------------------------------------------------------------------
| Id  | Operation               | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                  |   107 |  9844 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN              |                  |   107 |  9844 |     7  (15)| 00:00:01 |
|   2 |   VIEW                  | index$_join$_003 |    27 |   432 |     3  (34)| 00:00:01 |
|*  3 |    HASH JOIN            |                  |       |       |            |          |
|   4 |     INDEX FAST FULL SCAN| DEPT_ID_PK       |    27 |   432 |     1   (0)| 00:00:01 |
|   5 |     INDEX FAST FULL SCAN| DEPT_NAME_IDX    |    27 |   432 |     1   (0)| 00:00:01 |
|*  6 |   VIEW                  |                  |   107 |  8132 |     4   (0)| 00:00:01 | -- ②
|   7 |    COUNT                |                  |       |       |            |          |
|   8 |     TABLE ACCESS FULL   | EMPLOYEE         |   107 |  4173 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("A"."DEPARTMENT_ID"="B"."DEPARTMENT_ID")
   3 - access(ROWID=ROWID)
   6 - filter("A"."JOB_ID"='MK_REP') -- ②

-- A.JOB_ID = 'MK_REP' 조건이 View 외부로 밀려나 버렸다.
-- 결론 : FPD와 같은 간단한 Transformation의 경우에도 제약사항이 존재하므로 유의하자.




The Logical Optimizer  (bysql.net 2011년 2차 스터디)
작성자: 김범석 (darkbeom)
최초작성일: 2011년 9월 17일
본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net
문서의 잘못된 점이나 질문사항은 본문서에 댓글로 남겨주세요. ^^