查询计划中集的势（Cardinality）的计算
當使用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?
?
與50位技術專家面對面20年技術見證，附贈技術全景圖總結
以上是生活随笔為你收集整理的查询计划中集的势（Cardinality）的计算的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： dbms_xplan.display_c
- 下一篇： 从DUMP函数说开去