Cardinality的計算

在資料庫中如果我們使用CBO作為優化器，那麼一條SQl語句的執行就會有多種執行路徑，但是有一點需要注意，CBO會計算各個執行路徑的訪問代價，從而選擇代價最小的執行路徑作為我們的執行計劃。通過10053事件可以trace出這樣的一個過程。那麼CBO在做訪問路徑估算的時候有一個很重要的引數作為我們計算Cost的因數，這個就是我們通常所說的掃描欄位的集的勢(Cardinality).關於這個值的計算比較的複雜，這裡我先討論一下使用繫結變數的情況下集的勢的計算。

一般公式如下：

Cardinality=MAX(Cardinality Factor * Rowcount，1)

那麼直接影響我們Cardinality結果的的其實就是Cardinality因子(Cardinality Factor).這個因子是怎麼計算的呢？來看一下在不同情況下的Cardinality Factor的計算

***********
索引欄位
***********
如果我們在欄位上面建立了索引，或者是對多個欄位建立了一個複合索引。這個索引欄位的計算方式有多種

對於這個欄位的查詢條件是"="的情況下,那麼我們的Cardinality Factor的計算公式就如下：
------------------------------------------------------------------------------------------------------------

Cardinality Factor = 1 / 欄位上的唯一值的數量

來看個例子:

C:\>sqlplus /nolog

SQL*Plus: Release 9.2.0.8.0 - Production on 星期二 1月 15 12:50:38 2008

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> conn / as sysdba;
已連線。
SQL> create table t ( a number , b char(1), c char(5));

表已建立。

SQL> create index t_idx1 on t (b,c);

索引已建立。

SQL> begin
  2     for i in 1 .. 1000 loop
  3      insert into t values(i,mod(i,10),mod(i,13));
  4     end loop;
  5     commit;
  6  end;
  7  /

PL/SQL 過程已成功完成。

SQL> analyze table t compute statistics
  2  for table
  3  for all indexes
  4  for all indexed columns;

表已分析。

SQL> select count(distinct b) from t;

COUNT(DISTINCTB)
----------------
              10

SQL> select count(distinct c) from t;

COUNT(DISTINCTC)
----------------
              13

SQL> select count(*) from
  2  (select distinct b,c from t);

  COUNT(*)
----------
       130

我們來看看column b的cardinality

SQL> variable V char
SQL> exec :V := 8

PL/SQL 過程已成功完成。

SQL> set autotrace traceonly
SQL> select /*+index(t t_idx1)*/ * from t where b=:V;

已選擇100行。

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=41 Card=100 Bytes=15
          00)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=41 Card=100 Byt
          es=1500)

   2    1     INDEX (RANGE SCAN) OF 'T_IDX1' (NON-UNIQUE) (Cost=2 Card
          =100)

這裡我們可以看到索引的範圍掃描中 Card=100,
這個Cardinality的結果就是通過 round(1/10 * 1000) = 100,其中 1/10 這個10就是我們b列中唯一鍵的數量 得出的結果就是我們的集的式因子(Cardinality Factor)。需要注意的是(Cardinality Factor = 1/欄位上唯一值的數量)公式只適用於"="的情況下

我們來看看column c的cardinality

SQL> variable V char
SQL> exec :V := 3

PL/SQL 過程已成功完成。

SQL> select /*+index(t t_idx1)*/ * from t where c=:V;

已選擇77行。

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=35 Card=77 Bytes=115
          5)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=35 Card=77 Byte
          s=1155)

   2    1     INDEX (FULL SCAN) OF 'T_IDX1' (NON-UNIQUE) (Cost=5 Card=
          77)

這裡我們可以看到索引的全掃描中 Card=77,
這個Cardinality的結果就是通過 round(1/13 * 1000) = 100,其中 1/13 這個13就是我們c列中唯一鍵的數量 得出的結果就是我們的集的式因子(Cardinality Factor)。需要注意的是(Cardinality Factor = 1/欄位上唯一值的數量)公式只適用於"="的情況下

那麼如果索引欄位的查詢條件是"" "<=" ">=" 那麼我們計算Cardinality Factor的公式就為
------------------------------------------------------------------------------------------------------------

Cardinality Factor = (1 / 欄位上的唯一值的數量) + (1 / 記錄數)

比如：
SQL> select /*+index(t t_idx1)*/ * from t where b > :V;

已選擇600行。

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=101 Bytes=151
          5)

   1    0   TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=101 Bytes=1515)

這裡Cardnality就是等於[ (1/10) + (1/1000) ] * 1000 = 101

如果索引欄位的查詢條件是in,那麼我們的公式就為：
---------------------------------------------------------------------------

Cardinality Factor = in條件中的變數數 / 欄位上的唯一值的數量

比如:
SQL> variable A char
SQL> variable B char
SQL> variable C char
SQL> exec :A := 2

PL/SQL 過程已成功完成。

SQL> exec :B := 3

PL/SQL 過程已成功完成。

SQL> exec :C := 4

PL/SQL 過程已成功完成。

SQL> select /*+index(t t_idx10*/ * from t where b in (:A,:B,:C);

已選擇300行。

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=300 Bytes=450
          0)

   1    0   TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=300 Bytes=4500)

這裡我們可以看到全表掃描中 Card=300,
這個Cardinality的結果就是通過 round(3/10 * 1000) = 300,其中 3/10 這個3就是我們b列中in中的變數數得出的結果就是我們的集的式因子(Cardinality Factor)。

如果索引欄位的查詢條件是"<>",那麼我們的公式就為：
-----------------------------------------------------------------------------

Cardinality Factor = ( 1 - (1 / 欄位上的唯一值的數量 ) )

可以想想這是 "=" 的一個補集

比如
SQL> select /*+index(t t_idx1)*/ * from t where c <> :V;

已選擇923行。

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=365 Card=923 Bytes=1
          3845)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=365 Card=923 By
          tes=13845)

   2    1     INDEX (FULL SCAN) OF 'T_IDX1' (NON-UNIQUE) (Cost=5 Card=
          923)

在這裡我們可以看到索引的全掃描中 Card=923,
這個Cardinality的結果就是通過 round((1-1/13) * 1000) = 923,其中 1/13 這個13就是我們c列中唯一鍵的數量,其結果被1減去後得出的結果就是我們的集的式因子(Cardinality Factor)。

如果查詢條件為not in的時候，Cardinality的結果是根據not in中變數作為階乘得出的公式為：
------------------------------------------------------------------------------------------------------------

Cardinality Factor = ( 1 - (1 / 欄位上的唯一值的數量 ) ) ^ (not in 變數數)

SQL> select /*+index(t t_idx1)*/ * from t where b not in (:A,:B,:C);

已選擇700行。

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=290 Card=729 Bytes=1
          0935)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=290 Card=729 By
          tes=10935)

   2    1     INDEX (FULL SCAN) OF 'T_IDX1' (NON-UNIQUE) (Cost=5 Card=
          729)

在這裡我們可以看到索引的全掃描中 Card=729,
這個Cardinality的結果就是通過 round((1-1/10)^3 * 1000) = 729,其中 1/10 這個10就是我們b列中唯一鍵的數量,其結果被1減去後得出的結果,然後再^3,就是我們的集的式因子(Cardinality Factor)。

**************
非索引欄位
**************
當查詢條件為"="或者是"in"的時候,非索引欄位的Cardinality Factor的計算公式為：
-----------------------------------------------------------------------------------------------------------

Cardinality Factor = 1 / 100

比如：
SQL> select * from t where a = :V;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=10 Bytes=150)
   1    0   TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=10 Bytes=150)

在這裡我們可以看到索引的全掃描中 Card=10,
這個Cardinality的結果就是通過 round((1/100) * 1000) = 10

當查詢條件為"","<=",">=","<>","not in",非索引欄位的Cardinality Factor的計算公式為：
-------------------------------------------------------------------------------------------------------------

Cardinality Factor = 1 / 20

比如:

SQL> select * from t where a >= :V;

已選擇991行。

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=50 Bytes=750)
   1    0   TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=50 Bytes=750)

SQL> select * from t where a <= :V;

已選擇9行。

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=50 Bytes=750)
   1    0   TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=50 Bytes=750)

SQL> select * from t where a <> :V;

已選擇999行。

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=50 Bytes=750)
   1    0   TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=50 Bytes=750)

在這裡我們可以看到索引的全掃描中 Card=50,
這個Cardinality的結果就是通過 round((1/20) * 1000) = 50

********
多欄位
********
其公式只有一個就是：

Cardinality Factor = 欄位1的Cardinality Factor * 欄位2的Cardinality Factor * ... * 欄位n的Cardinality Factor

SQL> select /*+index(t t_idx1)*/ * from t where b > :V and c =:A;

已選擇46行。

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=6 Card=8 Bytes=120)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=6 Card=8 Bytes=
          120)

   2    1     INDEX (RANGE SCAN) OF 'T_IDX1' (NON-UNIQUE) (Cost=2 Card
          =101)

首先我們看b>:v的Cardinality Factor = (1/10) + (1/1000)
c=:A的Cardinality Factor = (1/13)
所以這個cardinality factor的結果就是 = 0.101 * 0.0769 = 0.0077669
那麼Cardinality = round(0.0077669  * 1000) = 8

**********
全表掃描
**********
Cardinality Factor=1;

我們以下查詢的Cardinality的結果就為：
SQL> select * from t;

已選擇1000行。

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=1000 Bytes=15
          000)

   1    0   TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1000 Bytes=15000)

恩，就是1*1000=1000

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