CBO（基於代價的最佳化器）是RBO（基於規則的最佳化器）的替代品，從9i開始oracle就建議使用者使用CBO來進行SQL的最佳化。CBO大概的最佳化原理很簡單，他透過物件上的統計資訊來計算各個執行計劃的代價，然後選擇代價較小的執行計劃來執行。所以對於CBO來說物件（比如表，索引）上的統計資訊就顯得十分的重要，不僅要有統計資訊，還要保證統計資訊是準確的，不準確的統計資訊可能會帶來災難性的結果。那麼oracle是怎麼樣利用這些統計資訊呢。下面舉個簡單的例子幫助大家理解：
SQL> create table sunwg (id number);
Table created.
SQL> insert into sunwg select rownum from all_tables where rownum<101;
100 rows created.
SQL> commit;
SQL> analyze table sunwg compute statistics for table;
Table analyzed.
sql> select num_rows,blocks,empty_blocks,avg_space,chain_cnt,avg_row_len
from user_tables where table_name = 'sunwg';
num_rows blocks empty_blocks avg_space chain_cnt avg_row_len
---------- ---------- ------------ ---------- ---------- -----------
100 1 6 6978 0 6
sql>select num_distinct,raw_to_number(low_value),raw_to_number(high_value),density,num_buckets
from user_tab_columns where table_name='sunwg';
no rows selected
我們建立了一張測試表sunwg，並且分析了表上的統計資訊，但是我沒有分析列id的統計資訊。
SQL> set autot traceonly exp
SQL> select * from sunwg where id = 1;
Execution Plan
----------------------------------------------------------
Plan hash value: 459567752
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| SUNWG | 1 | 6 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------
從這個SQL的執行計劃可以看出來，Oracle認為這個SQL會查詢出1條記錄。事實上呢，實際表中ID = 1的記錄也只有一條，那麼oracle估計得很正確。Oracle真的這麼聰明麼？我們可以想象一下，如果僅僅告訴你一個表中有100條記錄，然後問你在這個表中ID = 1的記錄有幾條，你能算得出來麼？答案是肯定的，你不瞭解資料的分佈情況，無法得知ID = 1的記錄數量，所以oracle得到的這個ID = 1的結果是“蒙”的。那麼我們接著看下面的例子。
SQL> select * from sunwg where id >= 1;
Execution Plan
----------------------------------------------------------
Plan hash value: 459567752
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 30 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| SUNWG | 5 | 30 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
這下oracle就徹底的露餡了，oracle估計ID > 1的結果是5行，但實際上應該是99條。其實這也不能怪oracle，因為你給他的資訊實在是太少了，為了最佳化的正常進行他必須要估計一個大概基數值才可以進行代價的計算。
Where 條件 Oracle估計記錄數表中實際記錄數 Oracle估算公式（猜想）
ID = 1 1 1 100 * 1%
ID > 1 5 99 100* 5%
ID >= 1 5 100 100* 5%
ID = 110 1 0 1
ID + 1 > 1 5 100 100* 5%
ID + 1 >= 1 5 100 100* 5%
ID + 1 > 1 AND ID + 1 > 1 5 100 100* 5%
ID > 1 AND ID < 50 1 48 100 *( 5%* 5%)
ID >80 OR ID <30 10 49 100*(5% + 5% - 5%* 5%)
從上面這個結果我們還看不出來到底oracle笨不笨，畢竟我們沒有把完備的統計資訊給他。下面我會統計表上列ID的資訊，看看有了列ID上的統計資訊後，oracle會有什麼樣子的表現。
SQL> analyze table sunwg compute statistics for columns id size 100;
Table analyzed.
sql>select num_distinct,raw_to_number(low_value),raw_to_number(high_value),density,num_buckets
from user_tab_columns where table_name = 'sunwg';
num_distinct raw_to_number(low_value) raw_to_number(low_value) density
------------ ------------------------ ------------------------- ---
100 1 100 0.01
這個時候已經可以看到列ID上的統計資訊了，在重複做上面的測試可以得到下面的結果：
Where 條件 Oracle估計記錄數表中實際記錄數 Oracle估算公式（猜想）
ID = 1 1 1 100 *1%
ID > 1 99 99 100*((100 – 1)/100)
ID >= 1 100 100 100*((100 – 1)/100 + 0.01)
ID = 110 1 0 1
ID + 1 > 1 5 100 100 * 5%
ID + 1 >= 1 5 100 100 * 5%
ID + 1 > 1 AND ID + 1 > 1 5 100 100 * 5%
ID > 1 AND ID < 50 48 48 100*((99/100)*(49/100))
ID >80 OR ID <30 44 49 100*(20/100+29/100 - (20/100)*(29/100))
上面的結果雖然比沒有分析列ID上的統計資訊之前要準確了一些，但是和實際還是有一些差距的。
在CBO裡面還有另外一個和基數對應的概念——選擇率，他們之間的關係應該是：
基數 = 總記錄數 * 選擇率
如果要計算基數的時候只要先算出大概的選擇率，然後在和記錄數相乘就可以得到基數資訊。
那麼選擇率應該怎麼計算呢？（以第二張表格中的資料為例說明）
例一：條件ID = 1
選擇率 = 1/100 = 0.01
基數 = 100 * 0.01 = 1
例二：條件ID >= 1
選擇率 = (100 – 1)/100 + 0.01 = 1
基數 = 100 * 1 = 100
例三：條件ID > 1 AND ID < 50
選擇率 = （（100 – 1）/100） * （（50 – 1）/100）= 0.48
基數 = 100 * 0.48 = 48
關於多個查詢的條件的時候有三個公式的：
P（A） AND P（B） = P（A）的選擇率 * P（B）的選擇率
P（A） OR P（B） = P（A）的選擇率 + P（B）的選擇率 - P（A）的選擇率 * P（B）的選擇率
NOT P（A） = 1 - P（A）的選擇率
有個地方我們需要特別留意一下，就是在收集列ID上的資訊的時候。analyze table sunwg compute statistics for columns id size 100;
在前面的例子裡面我們使用的是SIZE 100，如果這個SIZE不同的話，那麼分析出來的結果也是不一樣的。實際上我們是在收集列上的直方圖資訊，這個SIZE就是直方圖的BUCKET的數量，這個值對直方圖資訊的準確性有著很重要的影響。
有個地方我們需要特別留意一下，在沒有直方圖資訊的時候使用的是NUM_DISTINCT 也就是說前面的 1% 實際上是 1/NUM_DISTINCT = 1/100,而在有直方圖的時候使用的是 density。

oracle 查詢計劃中的基數cardinality概念(一)

相關文章