2.8_FPD_(Filter_Push_Down)
2011.09.17 21:46
2.2.8 FPD - 조건절을 뷰 내부로 이동시켜라.
-- 뷰 혹은 인라인뷰 바깥에서 뷰 내부의 컬럼들을 이용하여 조건절을 사용할때 조건절이 뷰 내부로 침투되는 현상
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
문서의 잘못된 점이나 질문사항은 본문서에 댓글로 남겨주세요. ^^
