查詢計劃中集的勢（Cardinality）的計算

jixuewen發表於2007-12-04

當使用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

如果索引欄位查詢條件是“<>”，則計算公式為，

這時的集的勢值也是這個欄位上可以達到的最大集的勢值。

例：當查詢條件為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
[@more@]轉載。

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

相關文章