OPTIMIZER TRANSITIVITY RETAIN - 日本エクセム株式会社 Oracleパラメータ

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

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
;

- 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）

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