| ||
| {code:SQL | title= 0. 예제 | borderStyle=solid} SELECT /*+ gather_plan_statistics */ e.* FROM employee e, department d WHERE e.department_id = d.department_id --DEPARTMENT_ID로 조인 AND e.department_id = 50; --d.department_id ='50'으로 바꾸어도 논리적으로 지장이없다. |
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
| 0 | SELECT STATEMENT | 1 | 45 | 00:00:00.01 | 14 | ||
| 1 | NESTED LOOPS | 1 | 45 | 45 | 00:00:00.01 | 14 | |
| INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | 1 | 1 | 00:00:00.01 | 1 |
| 3 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 1 | 45 | 45 | 00:00:00.01 | 13 |
| INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | 45 | 45 | 00:00:00.01 | 6 |
Predicate Information (identified by operation id):
2 - access("D"."DEPARTMENT_ID"=50)
4 - access("E"."DEPARTMENT_ID"=50)
Predicate Information에 (D.DEPARTMENT_ID=50)조건 생성|
|{code:SQL|title= 1. 변환된 SQL |borderStyle=solid}
SELECT /*+ gather_plan_statistics */
e.*
FROM employee e, department d
WHERE e.department_id = d.department_id
AND e.department_id = '50'
AND d.department_id='50';
2. 10053 Trace정보
**************************
Predicate Move-Around (PM)
**************************
PM: PM bypassed: Outer query contains no views.
PM: PM bypassed: Outer query contains no views.
query block SEL$1 (#0) unchanged
FPD: Considering simple filter push in query block SEL$1 (#0)
"E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID" AND "E"."DEPARTMENT_ID"=50
try to generate transitive predicate from check constraints for query block SEL$1 (#0)
finally: "E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID" AND "E"."DEPARTMENT_ID"=50 AND "D"."DEPARTMENT_ID"=50
FPD: transitive predicates are generated in query block SEL$1 (#0)
"E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID" AND "E"."DEPARTMENT_ID"=50 AND "D"."DEPARTMENT_ID"=50 --D.DEPARTMENT_ID=50조건이 추가적으로 생성
|
| title= _optimizer_transitivity_retain를 False로 적용 | borderStyle=solid} ALTER SESSION SET "_optimizer_transitivity_retain" = FALSE; |
ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
select /*+ gather_plan_statistics */
e.EMPLOYEE_ID, e.EMAIL, e.HIRE_DATE
from EMPLOYEE e, DEPARTMENT d
where e.DEPARTMENT_ID = d.DEPARTMENT_ID
and e.DEPARTMENT_ID = '50'
ALTER SESSION SET EVENTS '10053 trace name context off';
**************************
Predicate Move-Around (PM)
**************************
PM: PM bypassed: Outer query contains no views. -->PM실패 no views
PM: PM bypassed: Outer query contains no views.
query block SEL$1 (#0) unchanged
FPD: Considering simple filter push in query block SEL$1 (#0)
"E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID" AND "E"."DEPARTMENT_ID"=50 -->조인조건이 존재함
try to generate transitive predicate from check constraints for query block SEL$1 (#0)
finally: "D"."DEPARTMENT_ID"=50 AND "E"."DEPARTMENT_ID"=50 -->조인절이 없어지고 상수 조건만 존재
|* 주의사항
Operator Transformation이나 Tansive Predicate는 PM(Predicate Move Around)이 아니다.
Predicate Move-Around(PM)자리에 Transitive Predicate가 발생했지만 PM은 발생하지 않았다.(no view) (2.15장에서 설명)
PM은 2.15장에서 자세히 설명|