2.2.9 TP - 조인절을 이용하여 다른 테이블에 상수조건을 생성시켜라.

(참고 : 오라클성능고도화2권 조건절 이행)

-- 조인 조건을 이용한 조건절 전이. TP(Transitive Predicate)를 Transitive Closure라고 부르기도 한다.

ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';

SELECT /*+ GATHER_PLAN_STATISTICS */
       E.*
  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';

---------------------------------------------------------+-----------------------------------+
| Id  | Operation                     | Name             | Rows  | Bytes | Cost  | Time      |
---------------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT              |                  |       |       |     2 |           |
| 1   |  NESTED LOOPS                 |                  |    45 |  3285 |     2 |  00:00:01 |
| 2   |   INDEX UNIQUE SCAN           | DEPT_ID_PK       |     1 |     4 |     0 |           |
| 3   |   TABLE ACCESS BY INDEX ROWID | EMPLOYEE         |    45 |  3105 |     2 |  00:00:01 |
| 4   |    INDEX RANGE SCAN           | EMP_DEPARTMENT_IX|    45 |       |     0 |           |
---------------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - access("D"."DEPARTMENT_ID"=50) -- ①, ②
4 - access("E"."DEPARTMENT_ID"=50) -- ②
 
-- ② D.DEPARTMENT_ID = '50' 조건이 생성. 즉 아래 SQL과 같다.

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';           -- ①

**************************
Predicate Move-Around (PM)
**************************
PM:     PM bypassed: Outer query contains no views.                   --> PM이 실패함
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)
constraint: "E"."LAST_NAME" IS NOT NULL

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

■ Control 파라미터를 이용한 제어

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';
  
ALTER SESSION SET "_optimizer_transitivity_retain" = TRUE;  

---------------------------------------------------------+-----------------------------------+
| Id  | Operation                     | Name             | Rows  | Bytes | Cost  | Time      |
---------------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT              |                  |       |       |     2 |           |
| 1   |  NESTED LOOPS                 |                  |    45 |  1260 |     2 |  00:00:01 |
| 2   |   INDEX UNIQUE SCAN           | DEPT_ID_PK       |     1 |     4 |     0 |           |
| 3   |   TABLE ACCESS BY INDEX ROWID | EMPLOYEE         |    45 |  1080 |     2 |  00:00:01 |
| 4   |    INDEX RANGE SCAN           | EMP_DEPARTMENT_IX|    45 |       |     0 |           |
---------------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - access("D"."DEPARTMENT_ID"=50)
4 - access("E"."DEPARTMENT_ID"=50)
 
Content of other_xml column
===========================
  db_version     : 11.2.0.1
  parse_schema   : TLO
  plan_hash      : 2161741042
  plan_hash_2    : 3476897284
  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      OPT_PARAM('_optimizer_transitivity_retain' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX(@"SEL$1" "D"@"SEL$1" ("DEPARTMENT"."DEPARTMENT_ID"))
      INDEX_RS_ASC(@"SEL$1" "E"@"SEL$1" ("EMPLOYEE"."DEPARTMENT_ID"))
      LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")
      USE_NL(@"SEL$1" "E"@"SEL$1")
    END_OUTLINE_DATA
  */
 
_optimizer_transitivity_retain      = false
 
***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************
  *************************************
  PARAMETERS WITH ALTERED VALUES
  ******************************
Compilation Environment Dump
_optimizer_transitivity_retain      = false
Bug Fix Control Environment


**************************
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: "D"."DEPARTMENT_ID"=50 AND "E"."DEPARTMENT_ID"=50 --> 조인절이 사라지고 상수조건만 존재 ①

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