10.2解決了10.1以下的Transitive Closure變化

        如果在關聯列中新增一個額外的預測，10.1版本資料庫transitive Closure在處理SQL語句條件過程中，會剔除掉多餘的預測條件，例如：
                  where colA =colB
                  and      colA=5
 
        則在Transitive Closure處理過程中，將條件轉化成：
                 where  colA = 5
                  and     colB = 5
       而關聯條件colA=colB被剔除。

        以上的剔除在CBO計算cardinality的時候，可能造成計算的錯誤。10.2版本將不再把關聯條件剔除，所以其   轉發成：
              where colA = colB
                   and colA=5
                   and colB=5

以下是具體的實驗：

1、建立測試指令碼
SQL>execute dbms_random.seed(0)

SQL>create table t1
          as
          select
                 trunc(dbms_random.value(0, 30 ))    join1,
                 trunc(dbms_random.value(0, 50 ))    join2,
                 lpad(rownum,10)                v1,
                 rpad('x',100)                padding
           from
                 all_objects
           where
                 rownum <= 10000;
  
SQL>create table t2
          as
          select
                 trunc(dbms_random.value(0, 40 ))    join1,
                 trunc(dbms_random.value(0, 40 ))    join2,
                 lpad(rownum,10)                v1,
                 rpad('x',100)                padding
           from
                all_objects
           where
               rownum <= 10000;

2、統計分析（略）

3、10.1.0.2版本進行測試
SQL>set autotrace traceonly explain

SQL>select    t1.v1, t2.v1
          from
               t1,  t2
          where
                     t1.join1 = 20
          and    t2.join1 = t1.join1
          and    t2.join2 = t1.join2

 PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |  1667 | 56678 |    83   (4)| 00:00:02 |
|*  1 |  HASH JOIN                       |              |  1667 | 56678 |    83   (4)| 00:00:02 |
|*  2 |   TABLE ACCESS FULL   |      T2   |    250 |   4250 |    41   (3)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL   |       T1   |   333 |    5661 |    41   (3)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T2"."JOIN2"="T1"."JOIN2")
   2 - filter("T2"."JOIN1"=20)
   3 - filter("T1"."JOIN1"=20)
  
        從預測資訊中我們看出剔除了t2.join1 = t1.join1的預測，在cardinality的計算也因此發生了偏差， 具體計算如下：
 
 Join Selectivity(ID=3)= ((t1.num_rows - t1.null)/t1.num_rows)/30=1/30
 Join cardinality(ID=3)= 10000 * Join Selectivity(ID=3)
                       = 10000 * 1/30 =333
                      
 
 Join Selectivity(ID=2)= ((t2.num_rows - t2.null)/t1.num_rows)/40=1/40
 Join cardinality(ID=2)= 10000 * Join Selectivity(ID=2)
                       = 10000 * 1/40 =250
                      
 
Join Selectivity(ID=1)= ((t1.num_rows(join2)- t1.null(join2))/t1.num_rows(join2)) *
                                         ((t2.num_rows(join2)- t2.null(join2))/t2.num_rows(join2)) *
                                          1/max(t1.distinct_value(join2), t2.distinct_value(join2)
                                     = 1/max(40,50)=1/50

Join cardinality(ID=1)= Join cardinality(ID=3)* Join cardinality(ID=2)*
                                          Join Selectivity(ID=1)
                                      = 1000/30 * 1000/40 * 1/50
                                      = 1667
 
 在10.2.0.1 測試以上指令碼執行計劃如下：
 
Execution Plan
---------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |         52 |  1768 |    81   (2)| 00:00:01 |
|*  1 |  HASH JOIN                       |             |         52 |  1768 |    81   (2)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL  |      T2   |       250 |  4250 |    40   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL  |      T1   |        333 |  5661 |    40   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T2"."JOIN1"="T1"."JOIN1" AND "T2"."JOIN2"="T1"."JOIN2")
   2 - filter("T2"."JOIN1"=20)
   3 - filter("T1"."JOIN1"=20)
 
        我們發現ID=1出現了變化，並沒有剔除 t2.JOIN1=t1.JOIN1,所以CBO計算公式發生瞭如下變化
 Join cardinality(ID3)=10000/30
 Join cardinality(ID2)=10000/40
 
 Join Selectivity(ID3)=1/max(t1.distinct_value(join1)* t1.distinct_value(join2),
                             t2.distinct_value(join1)* t2.distinct_value(join2))
                      = 1/max(1500,1600)=1/1600          
 
 Join cardinality(ID3)= 10000/30 * 10000/40 * 1/1600
                      = 10000/(12*16)
                      = 10000/192
                      = 52
 
 所以在10.1版本以下，為了保證CBO能正確計算cardinality,語句需要修改為
 
 SQL>select    t1.v1, t2.v1
            from
                  t1,  t2
          where
                     t1.join1 = 20
           and    t2.join1 = t1.join1
           and    t2.join2 = t1.join2
           and   t2.join1 = 20;

總之一句話，隨著ORACLE版本升級，CBO計算越來越智慧了。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/354732/viewspace-623136/，如需轉載，請註明出處，否則將追究法律責任。