10.2 多列關聯cardianlity公式變化

        10.2版本在多列關聯時候，join cardinality計算公式已經產生變化。具體實驗如下：

1、建立測試表

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、不同資料庫版本測試
SQL>set autotrace traceonly explain
SQL>select    t1.v1, t2.v1
           from
                      t1,
                      t2
           where
                      t2.join1 = t1.join1
           and    t2.join2 = t1.join2;
 
Execution Plan（9.2.0.6執行計劃）
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=42 Card=50000 Bytes=1700000)
   1    0   HASH JOIN (Cost=42 Card=50000 Bytes=1700000)
   2    1     TABLE ACCESS (FULL) OF 'T1' (Cost=19 Card=10000 Bytes=170000)
   3    1     TABLE ACCESS (FULL) OF 'T2' (Cost=19 Card=10000 Bytes=170000)

Execution Plan（10.2.0.1執行計劃）
-------------------------------------------------------------------------------------
| Id  | Operation                             | Name | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |             | 62500 |  2075K|    46 |
|*  1 |  HASH JOIN                        |             | 62500 |  2075K|    46 |
|   2 |   TABLE ACCESS FULL    |      T1   | 10000 |    166K|    18 |
|   3 |   TABLE ACCESS FULL    |      T2   | 10000 |    166K|    18 |
-------------------------------------------------------------------------------------

4、計算公式分析
從中我們知道，在9.2.0.6中，多列關聯計算公式如下：

Join Selectivity = Join Sel (join1) * Join Sel (join2)
Join Sel (join1)=((t1.num_rows - t1.null)/t1.num_rows) *
                             ((t2.num_rows - t2.null)/t2.num_rows) *
                             1/MAX(num_distinct(t1.join1), num_distinct(t2.join1)
                           =(10000 -0)/10000* (10000-0)/10000 * 1/max(30, 40)
                           = 1/40
Join Sel(join2) = 1/50
Join Selectivity=1/40*1/50=1/2000

Join Cardinality = Join Selectivity * filter card(t1) * filter card(t2)
                            = 1/2000 * 10000 * 10000 =50000

但在10.2.0.1版本，cardinality= 62500
Join Selectivity 公式可能發生如下變化
    MAX(num_distinct(t1,join1)*num_distinct(t1.join2),     
              num_distinct(t2,join1)*num_distinct(t2.join2))
 
所以在計算中,
    Join selectivity = ((t1.num_rows - t1.null)/t1.num_rows) *
                                   ((t2.num_rows - t2.null)/t2.num_rows) /
                                    1/Max(40*40, 30*50)
                               = 1/1600
 
   Join cardinality = 1/1600 * 10000 * 10000 = 62500

引數_optimizer_join_sel_sanity_check 控制該動作

SQL> alter session set "_optimizer_join_sel_sanity_check" = false;

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

Execution Plan（10.2.0.1執行計劃）
-----------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost  |
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 50000 |  1660K|    44 |
|*  1 |  HASH JOIN         |      | 50000 |  1660K|    44 |
|   2 |   TABLE ACCESS FULL| T1   | 10000 |   166K|    18 |
|   3 |   TABLE ACCESS FULL| T2   | 10000 |   166K|    18 |
-----------------------------------------------------------

                           

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