查询计划中集的势（Cardinality）的计算

日期： 2007-06-07 14:50:13

分享到

当使用CBO模式的优化器时，oracle在生成查询计划时，会计算各个访问路径的代价，选择代价最小的访问路径作为查询计划。这个选择过程我们可以通过做一个10053的trace来观察。

在做代价估算时，有一个很重要的参数作为代价计算的因数，这就扫描字段的集的势（cardinality）。那么这个值是如何计算的呢？这个值的计算根据索引情况及查询条件不同而不同，因而它的计算也比较复杂。下面我们只讨论在使用绑定变量的情况下集的势的计算。

集的势总的计算公式是：

集的势 = MAX(集的势因子 * 记录数, 1)

可以看出，影响集的势的值的主要因素是集的势因子。在不同情况下，这个因子的计算公式不同，下面我们就讨论不同情况下的集的势因子的计算。

索引字段

对于建立了索引（可以是复合索引）的字段，如果查询条件是“＝”，字段的集的势计算公式如下：

集的势因子 = 1 / 字段上的唯一值数

让我们做个测试看，
SQL> create table T_PEEKING3 (a NUMBER, b char(1), c char(5));
 
Table created.
 
SQL>
SQL> create index T_PEEKING3_IDX1 on T_PEEKING3(b, c);
 
Index created.
 
SQL>
SQL> begin
  2    for i in 1..1000 loop
  3      insert into T_PEEKING3 values (i, mod(i, 10), mod(i, 13));
  4    end loop;
  5    commit;
  6  end;
  7  /
 
PL/SQL procedure successfully completed.
 
SQL>
SQL> analyze table T_PEEKING3 compute statistics for table for all indexes for all indexed columns;
 
Table analyzed.
表的记录数为1000，字段(B, C)上建立了索引，它们的唯一值数分别为：
SQL> select count(distinct b) from T_PEEKING3;
 
COUNT(DISTINCTB)
----------------
              10
 
SQL>
SQL> select count(distinct c) from T_PEEKING3;
 
COUNT(DISTINCTC)
----------------
              13
 
SQL>
SQL> select count(*) from
  2  (
  3  select distinct b, c from T_PEEKING3
  4  );
 
  COUNT(*)
----------
       130
因此，B字段的集的势为round(1/10 * 1000) = 100,
select /*+index(a T_PEEKING3_IDX1)*/ * from T_PEEKING3 a where b=:V;
 
SELECT STATEMENT, GOAL = CHOOSE                     Cost=55  Cardinality=100   Bytes=1500
 TABLE ACCESS BY INDEX ROWID       Object name=T_PEEKING3    Cost=55  Cardinality=100  Bytes=1500
  INDEX RANGE SCAN        Object name=T_PEEKING3_IDX1        Cost=1   Cardinality=100  
因此，C字段的集的势为round(1/13 * 1000) = 77,
select /*+index(a T_PEEKING3_IDX1)*/ * from T_PEEKING3 a where c=:V;
 
SELECT STATEMENT, GOAL = CHOOSE                     Cost=21  Cardinality=77    Bytes=1386
 TABLE ACCESS BY INDEX ROWID Object name=T_PEEKING3 Cost=21  Cardinality=77    Bytes=1386
  INDEX FULL SCAN Object name=T_PEEKING3_IDX1       Cost=3   Cardinality=77    
如果索引字段查询条件是“<”“>”“<=”“>=”，则计算公式为，

集的势因子 = (1 / 字段上的唯一值数) + (1/记录数)

例：当查询条件为c > :1，它的集的势为round((1/13 + 1/1000)*1000) = 78
select /*+index(a T_PEEKING3_IDX1)*/* from T_PEEKING3 a where c > :1
 
SELECT STATEMENT, GOAL = CHOOSE                     Cost=22  Cardinality=78    Bytes=1404
 TABLE ACCESS BY INDEX ROWID Object name=T_PEEKING3 Cost=22  Cardinality=78    Bytes=1404
  INDEX FULL SCAN Object name=T_PEEKING3_IDX1       Cost=3   Cardinality=78             
如果索引字段查询条件是in，则计算公式为，

集的势因子 = in条件中的变量数 / 字段上的唯一值数

例：当查询条件为c in (:1, :2, :3)，它的集的势为round(3/13 * 1000) = 231
select /*+index(a T_PEEKING3_IDX1)*/* from T_PEEKING3 a where  c in (:1, :2, :3);
 
SELECT STATEMENT, GOAL = CHOOSE                     Cost=57  Cardinality=231   Bytes=4158
 TABLE ACCESS BY INDEX ROWID Object name=T_PEEKING3 Cost=57  Cardinality=231   Bytes=4158
  INDEX FULL SCAN Object name=T_PEEKING3_IDX1       Cost=3   Cardinality=231   
如果索引字段查询条件是“<>”，则计算公式为，

集的势因子 = (1 – (1/字段上的唯一值数))

这时的集的势值也是这个字段上可以达到的最大集的势值。

例：当查询条件为c <> :1，它的集的势为round((1 – 1/13) * 1000) = 923
select /*+index(a T_PEEKING3_IDX1)*/* from T_PEEKING3 a where  c <> :1
 
SELECT STATEMENT, GOAL = CHOOSE                     Cost=219 Cardinality=923   Bytes=16614
 TABLE ACCESS BY INDEX ROWID Object name=T_PEEKING3 Cost=219 Cardinality=923   Bytes=16614
  INDEX FULL SCAN Object name=T_PEEKING3_IDX1       Cost=3   Cardinality=923   
当查询条件为not in时，计算就更为复杂了。它是根据not in中的变量值按阶计算的。

集的势因子 = (1 – (1/字段上的唯一值数))^(not in中变量数)

例：当查询条件为c not in (:1, :2, :3)，它的集的势为round((1 – 1/13)^3 * 1000) = 787
select /*+index(a T_PEEKING3_IDX1)*/* from T_PEEKING3 a where  c not in (:1, :2, :3)
 
SELECT STATEMENT, GOAL = CHOOSE                     Cost=187 Cardinality=787   Bytes=14166
 TABLE ACCESS BY INDEX ROWID Object name=T_PEEKING3 Cost=187 Cardinality=787   Bytes=14166
  INDEX FULL SCAN Object name=T_PEEKING3_IDX1       Cost=3   Cardinality=787   
非索引字段

当查询条件为 “=”、“in” 时，非索引字段的集的势因子是，

集的势因子 = 1/100

例：以下集的势为 1/100 * 1000 = 10
select * from T_PEEKING3 where a = :1;






SELECT STATEMENT, GOAL = CHOOSE                     Cost=2   Cardinality=10    Bytes=180
 TABLE ACCESS FULL Object name=T_PEEKING3   Cost=2   Cardinality=10    Bytes=180
当查询条件为“<”、“>”、“<=”、“>=”、“<>”、“not in” 时，非索引字段的集的势因子是，

集的势因子 = 1/20

例：以下集的势为 1/100 * 1000 = 10
select * from T_PEEKING3 where a < :1;






SELECT STATEMENT, GOAL = CHOOSE                     Cost=2   Cardinality=10    Bytes=180
 TABLE ACCESS FULL Object name=T_PEEKING3   Cost=2   Cardinality=50    Bytes=180
多字段

对于多个字段同时在查询条件中，集的势因子计算公式如下，

集的势因子 = 字段1的集的势因子 * 字段2的集的势因子 * … *字段n的集的势因子

例：以下两个字段的复合集的势为round(((1/10 + 1/1000)*(1/13)) * 1000) = 8,
select /*+index(a T_PEEKING3_IDX1)*/* from T_PEEKING3 a where  b > :1 and c = :2;
SELECT STATEMENT, GOAL = CHOOSE             Cost=4   Cardinality=8     Bytes=144
 TABLE ACCESS BY INDEX ROWID Object name=T_PEEKING3 Cost=4   Cardinality=8     Bytes=144
  INDEX RANGE SCAN        Object name=T_PEEKING3_IDX1        Cost=1   Cardinality=8             
例：以下查询的集的势为round(((3/10) * 1/13) * 1000) = 23
select /*+index(a T_PEEKING3_IDX1)*/* from T_PEEKING3 a where  b in (:1, :2, :3) and c = :2;
 
SELECT STATEMENT, GOAL = CHOOSE                     Cost=7   Cardinality=23    Bytes=414
 INLIST ITERATOR                                    
  TABLE ACCESS BY INDEX ROWID Object name=T_PEEKING3 Cost=7   Cardinality=23    Bytes=414
   INDEX RANGE SCAN Object name=T_PEEKING3_IDX1     Cost=1   Cardinality=23    
例：以下查询的集的势为round((2/10) * (1/13 + 1/1000) * 1000) = 16
select /*+index(a T_PEEKING3_IDX1)*/* from T_PEEKING3 a where  b in (:1, :2) and c > :2;
 
SELECT STATEMENT, GOAL = CHOOSE                     Cost=5   Cardinality=16    Bytes=288
 INLIST ITERATOR                                    
  TABLE ACCESS BY INDEX ROWID Object name=T_PEEKING3 Cost=5   Cardinality=16    Bytes=288
   INDEX RANGE SCAN Object name=T_PEEKING3_IDX1     Cost=1   Cardinality=16             
例：以下查询的集的势为round((2/10) * (3/13) * 1000) = 46
select /*+index(a T_PEEKING3_IDX1)*/* from T_PEEKING3 a where  b in (:1, :2) and c in (:1, :2, :3);
 
SELECT STATEMENT, GOAL = CHOOSE                     Cost=12  Cardinality=46    Bytes=828
 INLIST ITERATOR                                    
  TABLE ACCESS BY INDEX ROWID Object name=T_PEEKING3 Cost=12  Cardinality=46    Bytes=828
   INDEX RANGE SCAN Object name=T_PEEKING3_IDX1     Cost=1   Cardinality=46    
例：以下查询的集的势为round((1-1/10) * ((1- 1/13)^2) * 1000) = 767
select /*+index(a T_PEEKING3_IDX1)*/* from T_PEEKING3 a where  b <>:1  and c not in (:2, :3)
 
SELECT STATEMENT, GOAL = CHOOSE                     Cost=183 Cardinality=767   Bytes=13806
 TABLE ACCESS BY INDEX ROWID Object name=T_PEEKING3 Cost=183 Cardinality=767   Bytes=13806
  INDEX FULL SCAN Object name=T_PEEKING3_IDX1       Cost=3   Cardinality=767   
例：以下查询的集的势为round((1/20) * (1/13 + 1/1000) * 1000) = 4
select * from T_PEEKING3 where a not in (:1) and c > :2;
 
SELECT STATEMENT, GOAL = CHOOSE Cost=68     Cardinality=4    Bytes=732
 TABLE ACCESS FULL Object name=T_PEEKING3   Cost=2   Cardinality=4     Bytes=732        
全表扫描

对于全表扫描，如果没有查询条件时，

集的势因子 = 1

例：以下集的势为 1 * 1000 = 1000
select * from T_PEEKING3;
 
SELECT STATEMENT, GOAL = CHOOSE                     Cost=2   Cardinality=1000  Bytes=18000
 TABLE ACCESS FULL Object name=T_PEEKING3   Cost=2   Cardinality=1000  Bytes=18000
 

上一篇：TX锁（Transaction Lock）分析

下一篇：什么时候使用绑定变量性能反而更差

本类中的所有文章