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 |
-----------------------------------------------------------