（8）连接基数

1、基本的连接基数

oracle有两个公式用于连接基数的计算：

假设我们对表t1和t2进行连接，连接列分别是c1和c2。
Join Selectivity = 
      ((num_rows(t1) - num_nulls(t1.c1)) / num_rows(t1)) *
      ((num_rows(t2) - num_nulls(t2.c2)) / num_rows(t2)) /
      greater(num_distinct(t1.c1),num_distinct(t2.c2))      --greater 较大的
Join Cardinality = 
      Join Selectivity * 
      filtered cardinality(t1) * filtered cardinality(t2)

下面对其进行解释。

SQL> create table t1 
  2  as
  3  select
  4  trunc(dbms_random.value(0, 25))filter,
  5  trunc(dbms_random.value(0, 30))join1,
  6  lpad(rownum,10)v1,
  7  from('x',100)        padding
rpad('x',100)        padding
  8  from
  9    all_objects
 10  where 
 11    rownum <= 10000;

表已创建。

SQL> create table t2
  2  as
  3  select
  4  trunc(dbms_random.value(0, 50))filter,
  5  trunc(dbms_random.value(0, 40))join1,
  6  lpad(rownum,10)v1,
  7  rpad('x',100)padding
  8  from
  9  all_objects
 10  where
 11  rownum <= 10000;

表已创建。

SQL> begin
  2  dbms_stats.gather_table_stats(
  3  user,
  4  't1',
  5  cascade => true,
  6  estimate_percent => null,
  7  method_opt => 'for all columns size 1'
  8  );
  9  end;
 10  /

PL/SQL 过程已成功完成。

SQL> begin
  2  dbms_stats.gather_table_stats(
  3  user,
  4  't2',
  5  cascade => true,
  6  estimate_percent => null,
  7  method_opt => 'for all columns size 1'
  8  );
  9  end;
 10  /

PL/SQL 过程已成功完成。

在本例中我们有：
  t1.filter 25个不同的值
  t2.filter 50个不同的值
  t1.join1 30个不同的值
  t2.join1 40个不同的值

由于两个表都有10000行，因此t1的过滤基数将是400（10000行除以不同值的个数25），t2的过滤基数将是200（10000行除以不同值的个数50）。

因为任意一个表中都没有空值，因此根据连接基数公式可以得到：
Join Selectivity =
      ((10000 - 0) / 10000) *
      ((10000 - 0) / 10000) /
      greater(30,40) = 1/40
Join Cardinality = 1/40 * 400 * 200 = 2000

当通过autotrace运行时，可以看到下面的计划：

SQL> set autotrace traceonly explain;
SQL> select t1.v1, t2.v1
  2    from t1, t2
  3   where t1.filter = 1
  4     and t2.join1 = t1.join1
  5     and t2.filter = 1;

执行计划
----------------------
Plan hash value: 2959412835

---------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------
|   0 | SELECT STATEMENT   |      |  2000 | 68000 |    67   (2)| 00:00:01 |
|*  1 |  HASH JOIN         |      |  2000 | 68000 |    67   (2)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T2   |   200 |  3400 |    33   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T1   |   400 |  6800 |    33   (0)| 00:00:01 |
---------------------------------------

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

   1 - access("T2"."JOIN1"="T1"."JOIN1")
   2 - filter("T2"."FILTER"=1)
   3 - filter("T1"."FILTER"=1)

其中显示出表t1的过滤基数为400，表t2的过滤基数为200，并且连接基数为2000，与我们预测的结果相同。
下面将测试修改得复杂些，在t1表的连接列上每20行取一个空值，t2表的连接列上每30行取一个控制。

SQL> set autotrace off;
SQL> update t1 set join1 = null
  2  where mod(to_number(v1),20) = 0;

已更新500行。

SQL> commit;

提交完成。

SQL> update t2 set join1 = null
  2  where mod(to_number(v1),30) = 0;

已更新333行。

SQL> commit;

提交完成。

SQL> begin
  2  dbms_stats.gather_table_stats(
  3  user,
  4  't1',
  5  cascade => true,
  6  estimate_percent => null,
  7  method_opt => 'for all columns size 1'
  8  );
  9  end;
 10  /

PL/SQL 过程已成功完成。

SQL> begin
  2  dbms_stats.gather_table_stats(
  3  user,
  4  't2',
  5  cascade => true,
  6  estimate_percent => null,
  7  method_opt => 'for all columns size 1'
  8  );
  9  end;
 10  /

PL/SQL 过程已成功完成。

Join Selectivity =