【DB筆試面試635】在Oracle中，直方圖分為哪幾類？

• 2019 年 10 月 10 日
• 筆記

題目部分

在Oracle中，直方圖分為哪幾類？

♣

答案部分

Oracle數據庫里的直方圖使用了一種稱為Bucket（桶）的方式來描述目標列的數據分佈。Bucket（桶）是一個邏輯上的概念，相當於分組，每個Bucket就是一組，每個Bucket里會存儲一個或多個目標列中的數據。Oracle會用兩個維度來描述一個Bucket，這兩個維度分別是ENDPOINT_NUMBER和ENDPOINT_VALUE，Oracle會將每個Bucket的這兩個維度記錄在數據字典基表SYS.HISTGRM$中。列的直方圖的類型可以通過查詢視圖DBA_TAB_COL_STATISTICS的HISTOGRAM列來獲取，一般情況下包含3類，NONE（沒有直方圖）、FREQUENCY（頻率直方圖，也叫等頻直方圖）、HEIGHT BALANCED（高度平衡直方圖，也叫等高直方圖）。在Oracle 12c中，又新增了兩種類型的直方圖，分別是頂級頻率直方圖（Top Frequency Histogram）和混合直方圖（Hybrid Histogram），本書只討論頻率和高度平衡直方圖。

（1）頻率（Frequency，Freq）直方圖

在Oracle 12c之前，在目標列的數據分佈是傾斜的情況下（即存儲在數據字典里的目標列的DISTINCT值的數量小於目標表的記錄數），如果存儲在數據字典里描述目標列直方圖的Bucket的數量等於目標列的DISTINCT值的數量，那麼這種類型的直方圖就是頻率（Frequency）直方圖。頻率直方圖只適用於那些目標列的DISTINCT值數量小於或等於254的情形。需要注意的是，在Oracle 12c中，頻率直方圖所對應的Bucket的數量可以超過254。

對於頻率直方圖而言，目標列直方圖的Bucket的數量就等於目標列的DISTINCT值的數量，此時目標列有多少個DISTINCT值，Oracle在數據字典DBA_TAB_HISTOGRAMS、DBA_PART_HISTOGRAMS、DBA_SUBPART_HISTOGRAMS（分別對應於表、分區和子分區的直方圖統計信息）中就會存儲多少條記錄，每一條記錄就代表了對其中的一個Bucket的描述，上述數據字典中的字段ENDPOINT_VALUE記錄了這些DISTINCT值，而字段ENDPOINT_NUMBER則記錄了到此DISTINCT值為止總共有多少條記錄。需要注意的是，對頻率直方圖而言，ENDPOINT_NUMBER是一個累加值，可以用一條記錄的ENDPOINT_NUMBER值減去它上一條記錄的ENDPOINT_NUMBER值來得到這條記錄本身所對應的ENDPOINT_VALUE值的記錄數，SQL如下所示：

SELECT SAL,COUNT(*) COUNTS,SUM(COUNT(*)) OVER(ORDER BY SAL RANGE UNBOUNDED PRECEDING) CURR_ROWS FROM T_ST_20170604_LHR T GROUP BY T.SAL;--從表中獲取累加值  SELECT TABLE_NAME,COLUMN_NAME,ENDPOINT_NUMBER,ENDPOINT_VALUE,NVL((ENDPOINT_NUMBER-(LAG(ENDPOINT_NUMBER) OVER (ORDER BY ENDPOINT_VALUE))),ENDPOINT_NUMBER) COUNTS FROM DBA_TAB_HISTOGRAMS WHERE TABLE_NAME='T_ST_20170604_LHR';--從直方圖中獲取分組記錄數  

（2）高度平衡（Height Balanced，HtBal）直方圖

如果存儲在數據字典里描述目標列直方圖的Bucket的數量小於目標列的DISTINCT值的數量，那麼這種類型的直方圖就是高度平衡（Height Balanced）直方圖。在高度平衡直方圖中，執行計劃的列的選擇性沒有頻率直方圖精確，而在現實很多時候，列的唯一值是超過254的，那麼只能使用高度平衡直方圖。在高度平衡直方圖中，在DBA_TAB_HISTOGRAMS視圖中，EDNPOINT_NUMBER代表桶號，且自動省去EDNPOINT_VALUE值相同且ENDPOINT_NUMBER相鄰的桶的值。ENDPOINT_VALUE表示每一個桶中的最大值，而第一個桶記錄的是最小值（Bucket為0的行，即EDNPOINT_NUMBER為0的行）。重複出現為ENDPOINT_VALUE的值稱為Popular Value。若Popular Value所在記錄的ENDPOINT_NUMBER值和它上一條記錄的ENDPOINT_NUMBER值之間的差值越大，則意味着該Popular Value在目標表中所佔的比例也就越大，它所對應的Cardinality也就越大。在高度平衡直方圖中，除了最後1個桶可能包含的數據比其它的桶少以外，所有其它的桶包含相同數量的值，其值為目標表總記錄數除以Bucket的數量。

（3）頻率和高度平衡直方圖的比對

本文選自《Oracle程序員面試筆試寶典》，作者：小麥苗