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/，如需轉載，請註明出處，否則將追究法律責任。

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

相關文章