OPTIMIZER COST BASED TRANSFORMATION - 日本エクセム株式会社 Oracleパラメータ

Syntax：_OPTIMIZER_COST_BASED_TRANSFORMATION = ON|LINEAR|OFF
設定方法：・Parameter File
　　　　　・ALTER SYSTEM SET “_OPTIMIZER_COST_BASED_TRANSFORMATION” = ON
　　　　　・ALTER SESSION SET “_OPTIMIZER_COST_BASED_TRANSFORMATION” = ON

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

alter session set_optimizer_cost_based_transformation= off;

 - あるいは

select/*+ opt_param（'_ optimizer_cost_based_transformation'、 'off'）*/...

SELECT /*+ */ "T3"."C1" "C1","T4"."C2" "C2",COUNT(*) "CNT" FROM "UKJA"."T3" "T3",
"UKJA"."T4" "T4" WHERE "T3"."C1"="T4"."C1" GROUP BY "T3"."C1","T4"."C2"
Query block (04C7F7C8) unchanged
CVM:     CVM bypassed: Outer QB referencing ROWNUM

SELECT /*+ */ "T2"."C1" "C1","X"."C2" "C2","X"."CNT" "CNT",ROWNUM "R" FROM "UKJA"."T2" "T2", 
(SELECT /*+ */ "T3"."C1" "C1","T4"."C2" "C2",COUNT(*) "CNT" FROM "UKJA"."T3" "T3",
"UKJA"."T4" "T4" WHERE "T3"."C1"="T4"."C1" GROUP BY "T3"."C1","T4"."C2") "X" 
WHERE "T2"."C1"="X"."C1"
Query block (04C7FE60) unchanged
CVM:     CVM bypassed: ROWNUM column

PM: Considering predicate move-around in SEL$1 (#1).
PM:   Checking validity of predicate move-around in SEL$1 (#1).
PM:     PM bypassed: Reference to ROWNUM.
PM:   Passed validity checks.

1.MERGEヒント
2.NO_UNNESTヒント
3.NO_MERGEヒント
4.NO_QUERY_TRANSFORMATIONヒント
5.UNNESTヒント
6._OPTIMIZER_PUSH_PRED_COST_BASEDパラメータ