OPTIMIZER TRANSITIVITY RETAIN

Syntax   : _OPTIMIZER_TRANSITIVITY_RETAIN = TRUE|FALSE
設定方法  : ・Parameter File
           ・ALTER SYSTEM SET “_OPTIMIZER_TRANSITIVITY_RETAIN” = TRUE
　　　　　　・ALTER SESSION SET “_OPTIMIZER_TRANSITIVITY_RETAIN” = TRUE

バージョンとデフォルト
9iR2 : -
10gR1: -
10gR2: TRUE
11gr1: TRUE

Transitive Closureによって結合条件がなくなる現象を防止することができたかどうかを指定します。つまり、この値がTrueの場合Transitive Closureが発生しても、結合条件はなくなりません。

Transitive Closureと以下のようにPredicateを前（Transitivity）させて、不必要なPredicateをなくすことを意味します。

select count（*）
from t1、t2
where t1.c1= t2.c1 and
      t1.n1=5
;
==>上記のQueryがTransformationプロセスを介して下Queryに変換される。
select count（*）
from t1、t2
where t1.n1=5 and
      t2.n1=5
;

Transitive Closureは、一般的に性能に有利であるが、結合条件について適用されるときは、不利な方向への実行計画の変化を持って来るので10gR2から、基本的に結合条件については、Transitive Closureが適用されないようにオフの改善されたものです。

_OPTIMIZER_TRANSITIVITY_RETAINパラメータによる実行計画の変化は、次のとおりです。

- Join条件をなくす場合（10gR2以前のバージョンで基本）
alter session set "_optimizer_transitivity_retain" = false;

explain plan for
select count（*）
from t1、t2
where
  t1.c1 = t2.c1 and
  t1.c1 = 5
;
-------------------------------------------------- -----------------
| Id | Operation | Name | Rows | Bytes | Cost（％CPU）|
-------------------------------------------------- -----------------
| 0 | SELECT STATEMENT | | 1 | 6 | 4489（3）|
| 1 | SORT AGGREGATE | | 1 | 6 | |
| 2 | MERGE JOIN CARTESIAN | | 1000K | 5859K | 4489（3）|
| * 3 | TABLE ACCESS FULL | T1 | 1000 | 3000 | 6（0）|
| 4 | BUFFER SORT | | 1000 | 3000 | 4483（3）|
| * 5 | TABLE ACCESS FULL | T2 | 1000 | 3000 | 4（0）|
-------------------------------------------------- -----------------
                                                                   
Predicate Information（identified by operation id）：
-------------------------------------------------- - 
   3 - filter（ "T1"。 "C1" = 5）
   5 - filter（ "T2"。 "C1" = 5）
 

- 結合条件を絶やさない場合（10gR2から基本）
alter session set "_optimizer_transitivity_retain" = true;

explain plan for
select count（*）
from t1、t2
where
  t1.c1 = t2.c1 and
  t1.c1 = 5
;
 -------------------------------------------------- ---------------
| Id | Operation | Name | Rows | Bytes | Cost（％CPU）|
-------------------------------------------------- ---------------
| 0 | SELECT STATEMENT | | 1 | 6 | 13（8）|
| 1 | SORT AGGREGATE | | 1 | 6 | |
| * 2 | HASH JOIN | | 100K | 585K | 13（8）|
| * 3 | TABLE ACCESS FULL | T1 | 1000 | 3000 | 6（0）|
| * 4 | TABLE ACCESS FULL | T2 | 1000 | 3000 | 6（0）|
-------------------------------------------------- ---------------
                                                                 
Predicate Information（identified by operation id）：
-------------------------------------------------- - 
   2 - access（ "T1"。 "C1" = "T2"。 "C1"）
   3 - filter（ "T1"。 "C1" = 5）
   4 - filter（ "T2"。 "C1" = 5）

10gR2以前のバージョンでは10gR2のような効果を得るためには、次のようにSQL文を変更する必要があります。

explain plan for
select count(*)
from t1, t2
where 
  t1.c1 = t2.c1 and
  t1.c1 = 5 and
  t2.c1 = 5 ;