直方圖，一種特殊類型的列的統計資訊，它能提供表中列的更詳細的資料分布資訊，直方圖将值存放于桶（buckets）中。基于不同值的數目和資料的分布，資料庫選擇要建立的直方圖類型，直方圖的類型有如下幾種：
- 頻率直方圖和頂頻直方圖：Frequency histograms and to frequency histograms；
- 高度平衡直方圖（遺留）：Height-Balanced hitograms；
- 混合柱狀圖：Hybrid histograms；
1 直方圖介紹
1.1 使用直方圖的目的
預設情況下，優化器假定列的不同值之間時均勻分布的。對于包含資料傾斜列（列中資料的分布不均勻的列），直方圖使優化器能夠為涉及這些列的過濾或連接配接謂詞生成更準确的基數的估計值，進而生成更精确的執行計劃。
1.2 何時資料庫建立直方圖
使用DBMS_STATS搜集表的統計資訊，查詢引用表中的列時，資料庫會根據之前的查詢負載來自動的建立直方圖。基本過程如下：
- 使用DBMS_STATS搜集表的統計資訊，且指定METHOD_OPT參數預設為SIZE AUTO；
- 使用者查詢對應的表；
- 資料庫記錄前面查詢時使用的謂詞，并更新資料字典表SYS.COL_USAGE$；
- 再次運作DBMS_STATS時，DBMS_STATS會查詢SYS.COL_USAGE$視圖并根據前面的查詢負載決定哪些列需要直方圖。
示例：
1）建立測試表
SQL> create table sh.sales_new as select * from sh.sales;
Table created. 2）檢視統計資訊
SQL> select column_name,notes,histogram from dba_tab_col_statistics where owner='SH' and table_name='SALES_NEW';
COLUMN_NAME NOTES HISTOGRAM
------------------------------ ------------------------------ ---------------
AMOUNT_SOLD STATS_ON_LOAD NONE
QUANTITY_SOLD STATS_ON_LOAD NONE
PROMO_ID STATS_ON_LOAD NONE
CHANNEL_ID STATS_ON_LOAD NONE
TIME_ID STATS_ON_LOAD NONE
CUST_ID STATS_ON_LOAD NONE
PROD_ID STATS_ON_LOAD NONE
7 rows selected. 3）執行查詢
SQL> select column_name,notes,histogram from dba_tab_col_statistics where owner='SH' and table_name='SALES_NEW';
COLUMN_NAME NOTES HISTOGRAM
------------------------------ ------------------------------ ---------------
AMOUNT_SOLD STATS_ON_LOAD NONE
QUANTITY_SOLD STATS_ON_LOAD NONE
PROMO_ID STATS_ON_LOAD NONE
CHANNEL_ID STATS_ON_LOAD NONE
TIME_ID STATS_ON_LOAD NONE
CUST_ID STATS_ON_LOAD NONE
PROD_ID STATS_ON_LOAD NONE
7 rows selected. 4）搜集統計資訊
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('SH','SALES_NEW',OPTIONS=>'GATHER AUTO');
PL/SQL procedure successfully completed. 5）檢視統計資訊
SQL> select column_name,notes,histogram from dba_tab_col_statistics where owner='SH' and table_name='SALES_NEW';
COLUMN_NAME NOTES HISTOGRAM
------------------------------ ------------------------------ ---------------
AMOUNT_SOLD STATS_ON_LOAD NONE
QUANTITY_SOLD STATS_ON_LOAD NONE
PROMO_ID STATS_ON_LOAD NONE
CHANNEL_ID STATS_ON_LOAD NONE
TIME_ID STATS_ON_LOAD NONE
CUST_ID STATS_ON_LOAD NONE
PROD_ID HISTOGRAM_ONLY FREQUENCY
7 rows selected. 6）檢視列的使用
SQL> select * from sys.col_usage$ where obj#=93264;
OBJ# INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP
---------- ---------- -------------- -------------- ----------------- ----------- ---------- ---------- ---------
93264 1 1 0 0 0 0 0 25-APR-20 1.3 如何選擇直方圖類型
前面講過，直方圖有多種類型，那麼建立直方圖時，資料庫如何選擇直方圖的類型呢？這裡有幾個參考變量：
- NDV：表示列的不同值的數量；
- n：表示直方圖桶（buckets）的數量，預設時254；
- p：表示内部百分比門檻值，等于(1-(1/n))*100；
- DBMS_STATS中estimate_percent參數是否設定為auto_sample_size（預設值）。
下圖展示的是直方圖建立時的決策樹：
2 直方圖基數算法
對于直方圖，基數的算法取決于端點數和值等因素，以及列值是否受歡迎。
2.1 端點編号和值（Endpoint Numbers and Values）
端點編号是唯一辨別桶的編号，在頻率和混合直方圖中，端點編号是目前桶和之前桶中包含的所有值的累計頻率，例如：端點編号是100的桶表示目前桶和以前所有桶的值的總頻率是100，在高度平衡的直方圖中，優化器按順序給桶編号，從0或1開始。在所有情況下，端點編号就是桶号。
端點值是桶中值範圍内的最大值，例如，如果一個桶隻包含52794和52795，那麼端點值是52795。
2.2 受歡迎和不受歡迎值（Popular and Nopopular Values）
直方圖中某個值的受歡迎程度會影響基數估值算法，具體如下：
- 受歡迎值：受歡迎值出現在多個桶的端點值，優化器通過檢查某個值是否是桶的端點值來确定該值是否受歡迎，如果是，那麼對于頻率直方圖，優化器将從目前桶的端點數減去前一個桶的端點數，混合直方圖存儲了每個端點的資訊，如果這個值大于1，那麼該值是受歡迎的。對于受歡迎的值，優化器通過下面的公式計算基數估計：cardinality of popular value = (num of rows in table) * (num of endpoints spanned by this value / total num of endpoints)；
- 不受歡迎值：所有不是受歡迎的值都是不受歡迎的值，對于不受歡迎的值，，優化器通過下面的公式計算基數估計：cardinality of nonpopular value = (num of rows in table) * density。
2.3 桶壓縮（Bucket Compression）
在某些情況下，為了減少桶的總數，優化器将多個桶壓縮到一個桶中，例如，下面的頻率直方圖表示第一個桶數是1，最後一個桶數是23：
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
1 52792
6 52793
8 52794
9 52795
10 52796
12 52797
14 52798
23 52799 可以看到，有幾個桶“丢失”了，最初，桶2到桶6每個都包含一個值為52793的執行個體，優化器将所有這些桶壓縮到具有最高端點數（桶6）的桶中，該桶現在包含5個執行個體的值52793，這個值是受歡迎的，因為目前桶和前一個桶的端點數之差為5，是以，在壓縮之前，52793是5個桶的端點。下圖展示了哪些桶是壓縮的，哪些值是受歡迎的：
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
1 52792 -> nonpopular
6 52793 -> buckets 2-6 compressed into 6; popular
8 52794 -> buckets 7-8 compressed into 8; popular
9 52795 -> nonpopular 10 52796 -> nonpopular
12 52797 -> buckets 11-12 compressed into 12; popular
14 52798 -> buckets 13-14 compressed into 14; popular
23 52799 -> buckets 15-23 compressed into 23; popular 3 頻率直方圖
在頻率直方圖中，每個不同的列值對應一個直方圖桶，由于每個值都有自己的專用桶，是以有些桶會有很多值，而有些則很少。
3.1 頻率直方圖滿足的條件
當滿足下面的條件時，資料庫建立頻率直方圖：
- NDV少于或等于桶數（預設為254）；
- DBMS_STATS對應的過程的參數設定為AUTO_SAMPLE_SIZE或指定一個具體的值；
3.2 生成頻率直方圖
本實驗在sh.countries_new的列country_subregion_id産生頻率直方圖。
1）生成測試資料
SQL> create table sh.countries_new as select * from sh.countries;
Table created.
SQL> select country_subregion_id,count(1) from sh.countries_new group by country_subregion_id order by 1;
COUNTRY_SUBREGION_ID COUNT(1)
-------------------- ----------
52792 1
52793 5
52794 2
52795 1
52796 1
52797 2
52798 2
52799 9
8 rows selected. 2）搜集統計資訊
begin
dbms_stats.gather_table_stats(ownname => 'SH',
tabname => 'COUNTRIES_NEW',
method_opt => 'for columns country_subregion_id');
end;
/
PL/SQL procedure successfully completed. 3）檢視列統計資訊
SQL> select column_name,notes,histogram from dba_tab_col_statistics where owner='SH' and table_name='COUNTRIES_NEW';
COLUMN_NAME NOTES HISTOGRAM
------------------------------ ------------------------------ ---------------
COUNTRY_NAME_HIST STATS_ON_LOAD NONE
COUNTRY_TOTAL_ID STATS_ON_LOAD NONE
COUNTRY_TOTAL STATS_ON_LOAD NONE
COUNTRY_REGION_ID STATS_ON_LOAD NONE
COUNTRY_REGION STATS_ON_LOAD NONE
COUNTRY_SUBREGION_ID FREQUENCY
COUNTRY_SUBREGION STATS_ON_LOAD NONE
COUNTRY_NAME STATS_ON_LOAD NONE
COUNTRY_ISO_CODE STATS_ON_LOAD NONE
COUNTRY_ID STATS_ON_LOAD NONE
10 rows selected. 可看到COUNTRY_SUBREGION_ID已搜集了直方圖資訊。
4）檢視直方圖資訊
select t.endpoint_number, t.endpoint_value
from dba_histograms t
where t.owner = 'SH'
and t.table_name = 'COUNTRIES_NEW'
and t.column_name = 'COUNTRY_SUBREGION_ID';
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
1 52792
6 52793
8 52794
9 52795
10 52796
12 52797
14 52798
23 52799
8 rows selected. 5）優化器評估52799的基數
SQL> select count(1) from sh.countries_new;
COUNT(1)
----------
23
SQL> select count(1) from sh.countries_new where country_subregion_id=52799;
COUNT(1)
----------
9 cardinality of popular value = (num of rows in table) * (num of endpoints spanned by this value / total num of endpoints)
即：C=23*(9/23)=9
和查詢的結果相同
4 最高頻率直方圖
最高頻率直方圖是頻率直方圖的一種變種，它忽略了統計上不重要的不受歡迎的值。
4.1 最高頻率直方圖滿足的條件
如果一小部分值占了大部分行，那麼在這一小部分值上建立一個頻率直方圖是很有用的，即使NDV大于請求的直方圖的桶的數量。為受歡迎的值建立一個更高品質的直方圖，優化器将忽略不受歡迎的值并建立一個直方圖。
當滿足下面的條件時，資料庫建立最高頻率直方圖：
- NDV大于直方圖桶的數量（預設為254）；
- 前n個頻率值占用的行數百分比等于或大于門檻值p，p等于(1-（1/n))*100；
- BMS_STATS對應的過程的參數設定為AUTO_SAMPLE_SIZE；
4.2 生成最高頻率直方圖
SQL> select country_subregion_id,count(1) from sh.countries_new group by country_subregion_id order by 1;
COUNTRY_SUBREGION_ID COUNT(1)
-------------------- ----------
52792 1
52793 5
52794 2
52795 1
52796 1
52797 2
52798 2
52799 9
8 rows selected. begin
dbms_stats.gather_table_stats(ownname => 'SH',
tabname => 'COUNTRIES_NEW',
method_opt => 'for columns country_subregion_id size 7 ');
end;
/
PL/SQL procedure successfully completed. SQL> select column_name,notes,histogram from dba_tab_col_statistics where owner='SH' and table_name='COUNTRIES_NEW';
COLUMN_NAME NOTES HISTOGRAM
------------------------------ ------------------------------ ---------------
COUNTRY_NAME_HIST STATS_ON_LOAD NONE
COUNTRY_TOTAL_ID STATS_ON_LOAD NONE
COUNTRY_TOTAL STATS_ON_LOAD NONE
COUNTRY_REGION_ID STATS_ON_LOAD NONE
COUNTRY_REGION STATS_ON_LOAD NONE
COUNTRY_SUBREGION_ID TOP-FREQUENCY
COUNTRY_SUBREGION STATS_ON_LOAD NONE
COUNTRY_NAME STATS_ON_LOAD NONE
COUNTRY_ISO_CODE STATS_ON_LOAD NONE
COUNTRY_ID STATS_ON_LOAD NONE
10 rows selected. 可看到COUNTRY_SUBREGION_ID已搜集了最高頻率直方圖（ TOP-FREQUENCY）資訊。
select t.endpoint_number, t.endpoint_value
from dba_histograms t
where t.owner = 'SH'
and t.table_name = 'COUNTRIES_NEW'
and t.column_name = 'COUNTRY_SUBREGION_ID';
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
1 52792
6 52793
8 52794
9 52796
11 52797
13 52798
22 52799
7 rows selected. 5 高度平衡直方圖（遺留）
在高度平衡直方圖中，将列值劃分為桶，以便每個桶包含大約相同的資料行。
待續。。。