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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/354732/viewspace-623136/