直方圖、基數、選擇性、群集因子

基本概念

基數(Cardinality) 列唯一鍵(Distinct_keys)的數量，比如性別，該列只有男女之分，所以這一列基數是2。

選擇性(Selectivity) 列唯一鍵(Distinct_Keys)與行數(Num_Rows)的比值。

直方圖 （Histogram）是一種對資料分佈質量情況進行描述的工具。它會按照某一列不同值出現數量多少，以及出現的頻率高低來繪製資料的分佈情況，以便能夠指導最佳化器根據資料的分佈做出正確的選擇。

頻率直方圖(FREQUENCY HISTOGRAM)，當列中Distinct_keys 較少(小於254)，如果不手工指定直方圖桶數(BUCKET)，Oracle就會自動的建立頻率直方圖，並且桶數(BUCKET)等於Distinct_Keys。

高度平衡直方圖(HEIGHT BALANCED)，當列中Distinct_keys大於254，如果不手工指定直方圖桶數(BUCKET)，Oracle就會自動的建立高度平衡直方圖。

叢集因子(Clustering Factor) 描述一個表中的列是否是規則排序的。

我們知道可以透過dbms_rowid.rowid_block_number(rowid)找到記錄對應的block 號。索引中記錄了rowid，因此oracle 就可以根據索引中的rowid來判斷記錄是否是在同一個block 中。舉個例子，比如說索引中有a,b,c,d,e五個記錄，首先比較a,b 是否在同一個block，如果不在同一個block 那麼Clustering Factor +1，然後繼續比較b,c 同理，如果b,c 不在同一個block，那麼Clustering Factor+1，這樣一直進行下去，直到比較了所有的記錄。根據演算法我們就可以知道clustering factor 的值介於block 數和錶行數之間。如果clustering factor 接近block 數，說明表的儲存和索引儲存排序接近，也就是說表中的記錄很有序，這樣在做index range scan 的時候能，讀取少量的data block 就能得到我們想要的資料，代價比較小。如果clustering factor 接近表記錄數，說明表的儲存和索引排序差異很大，在做index range scan 的時候，會額外讀取多個block，因為表記錄分散，代價較高。

1. 建立實驗表
SQL> show user;
USER is "ANDY"

SQL>create table test as select * from dba_objects;

2. 先收集統計資訊

BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'ANDY',
tabname => 'TEST',
estimate_percent => 100,
method_opt => 'for all columns size skewonly',
no_invalidate => FALSE,
degree => 1,
cascade => TRUE);
END;
/

說明：對於大表 estimate_percent 引數一般指定為 30% ，夠CBO用就行。

補充內容
刪統計資訊 （這裡不要操作，作為了解）
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'ANDY',
tabname => 'TEST',
estimate_percent => 100,
method_opt => 'for all columns size 1',
no_invalidate => FALSE,
degree => 1,
cascade => TRUE);
END;
/

3.查詢統計資訊 (基數和選擇性)

select a.column_name,
b.num_rows,
a.num_distinct Cardinality,
round(a.num_distinct / b.num_rows * 100, 2) selectivity,
a.histogram,
a.num_buckets
from dba_tab_col_statistics a, dba_tables b
where a.owner = b.owner
and a.table_name = b.table_name
and a.owner = 'ANDY'
and a.table_name = 'TEST';

COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS
------------------------------ ---------- ----------- ----------- --------------- -----------
OWNER 74770 30 .04 FREQUENCY 30
OBJECT_NAME 74770 46694 62.45 HEIGHT BALANCED 254
SUBOBJECT_NAME 74770 51 .07 FREQUENCY 51
OBJECT_ID 74770 74770 100 NONE 1
DATA_OBJECT_ID 74770 9792 13.1 HEIGHT BALANCED 254
OBJECT_TYPE 74770 43 .06 FREQUENCY 43
CREATED 74770 1120 1.5 HEIGHT BALANCED 254
LAST_DDL_TIME 74770 1185 1.58 HEIGHT BALANCED 254
TIMESTAMP 74770 1240 1.66 HEIGHT BALANCED 254
STATUS 74770 2 0 FREQUENCY 2
TEMPORARY 74770 2 0 FREQUENCY 2

COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS
------------------------------ ---------- ----------- ----------- --------------- -----------
GENERATED 74770 2 0 FREQUENCY 2
SECONDARY 74770 2 0 FREQUENCY 2
NAMESPACE 74770 20 .03 FREQUENCY 20
EDITION_NAME 74770 0 0 NONE 0

15 rows selected.

觀察得到：
如果 CARDINALITY 基數小於254 ，那麼 NUM_BUCKETS 桶數 就= 列基數 CARDINALITY。

總結：

1.
在OLTP系統中，基數/選擇性高的列，適合建立B-Tree索引，選擇性低的列不適合建立索引。
在OLAP環境中，基數低的列根據需求，可能會建立bitmap索引。

2.
沒有直方圖，CBO認為這個資料是分佈均勻的，執行計劃中估算返回的行數是基於列基數的平均值，
與實際返回的行數不符，可能產生錯誤的執行計劃。

3.
什麼時候該執行統計直方圖操作 -> 執行計劃估算的行數和實際查詢返回的行數進行比較，如果相差很大，則需。