Oracle的直方圖

1 直方圖的含義

在Oracle資料庫中，CBO會預設認為目标列的資料在其最小值LOW_VALUE和最大值HIGH_VALUE之間均勻分布的，并且會按照這個均勻分布原則來計算對目标列施加查詢條件後的可選擇率以及結果集的Cardinality，進而據此來計算成本值并選擇執行計劃。但目标列的資料是均勻分布這個原則并不總是正确的，在實際的系統中，我們很容易就能看到一些目标列的資料分布是不均勻的，甚至是極度傾斜、分布極度不均衡的。對這樣的列如果還按照均勻分布的原則去計算可選擇率與Cardinality，并據此來計算成本、選擇執行計劃，那麼CBO所選擇的執行計劃就可能是不合理的，甚至是錯誤的。

看一個由于資料分布極不均衡而導緻CBO選錯執行計劃的例子：

<code>zx@ORCL></code><code>create</code> <code>table</code> <code>t1 (a number(5),b varchar2(5));</code>

<code>Table</code> <code>created.</code>

<code>zx@ORCL></code><code>declare</code> <code>cnt number(5) := 1;</code>

<code> </code><code>2 </code><code>begin</code>

<code> </code><code>4 </code><code>insert</code> <code>into</code> <code>t1 </code><code>values</code><code>(1,</code><code>'1'</code><code>);</code>

<code> </code><code>10 </code><code>insert</code> <code>into</code> <code>t1 </code><code>values</code><code>(2,</code><code>'2'</code><code>);</code>

<code> </code><code>11 </code><code>commit</code><code>;</code>

<code>PL/SQL </code><code>procedure</code> <code>successfully completed.</code>

<code>zx@ORCL></code><code>select</code> <code>b,</code><code>count</code><code>(*) </code><code>from</code> <code>t1 </code><code>group</code> <code>by</code> <code>b;</code>

<code>B </code><code>COUNT</code><code>(*)</code>

<code>zx@ORCL></code><code>create</code> <code>index</code> <code>t1_ix_b </code><code>on</code> <code>t1(b);</code>

<code>Index</code> <code>created.</code>

對表T1不收集直方圖統計資訊的方式收集一下統計資訊：

<code>zx@ORCL></code><code>exec</code> <code>dbms_stats.gather_table_stats(</code><code>USER</code><code>,</code><code>'T1'</code><code>,estimate_percent=>100,method_opt=></code><code>'for all columns size 1'</code><code>);</code>

<code>zx@ORCL></code><code>select</code> <code>* </code><code>from</code> <code>t1 </code><code>where</code> <code>b=</code><code>'2'</code><code>;</code>

<code>zx@ORCL></code><code>select</code> <code>* </code><code>from</code> <code>table</code><code>(dbms_xplan.display_cursor(</code><code>null</code><code>,</code><code>null</code><code>,</code><code>'all'</code><code>));</code>

<code>PLAN_TABLE_OUTPUT</code>

<code>SQL_ID 5p7b772tpcvm4, child number 0</code>

<code>select</code> <code>* </code><code>from</code> <code>t1 </code><code>where</code> <code>b=</code><code>'2'</code>

<code>Plan hash value: 3617692013</code>

<code>|* 1 | </code><code>TABLE</code> <code>ACCESS </code><code>FULL</code><code>| T1 | 5001 | 25005 | 7 (0)| 00:00:01 |</code>

<code>.....省略部分輸出</code>

從執行計劃可以看出執行計劃走的是全表掃描，但是很顯然應該走索引T1_IX_B。這是因為CBO預設認為列B的資料是均勻分布的，而列B上的distinct值隻有1和2這兩值，是以CBO評估出來的對列B施加等值查詢條件的可選擇率就是1/2,進而評估出來對列B施加等值查詢條件的結果集的Cardinality就是5001：

<code>zx@ORCL></code><code>select</code> <code>round(10001*(1/2)) </code><code>from</code> <code>dual;</code>

<code>ROUND(10001*(1/2))</code>

正因為CBO評估出上述等值查詢要傳回結果集的Cardinality是5001，已經占了表T1總記錄數的一半，是以CBO認為此時再走列B上的索引T1_IX_B就已經不合适了，進而就選擇了對列T1的全表掃描。但實際上，CBO對上述等值查詢要傳回結果集的Cardinality的評估已經與事實嚴重不符，評估出來的值是5001，其實卻隻有1，差了好幾個數量級。

CBO這裡選擇了執行計劃，正确的執行計劃應該是走索引T1_IX_B。CBO選錯執行計劃的根本原因是表T1的列B的分布實際上是極度不均衡的(列B一共就兩值，其中10000個1，隻有1個2)，CBO在評估的一開始所用的原則就錯了，當然結果也就錯了。

為了解決上述問題，Oracle引入了直方圖(Histogram)。直方圖是一種特殊的列統計資訊，它較長的描述了目标列的資料分布情況。直方圖實際上存儲在資料字典基表HISTGRM$中，可以通過資料字典DBA_TAB_HISTOGRAMS、DBA_PART_HISTOGRAMS和DBA_SUBPART_HISTOGRAMS來分别檢視表、分區表的分區和分區表的子分區的直方圖統計資訊。

如果對目标列收集了直方圖，則意味着CBO将不再認為該目标列上的資料是均勻分布的了，CBO就會用該目标列上的直方圖統計資訊來計算對該列施加查詢條件後的可選擇率和傳回結果集的Cardinality，進而據此計算成本并選擇相應的執行計劃。

還用上面的例子，對表T1的列B收集了直方圖統計資訊後，CBO正确地評估出了傳回結果集的Cardinality不是5001而是1，進而就正确地選擇了走索引T1_IX_B的執行計劃：

<code>#清空shared_pool,生産系統不要随便執行</code>

<code>zx@ORCL></code><code>alter</code> <code>system flush shared_pool;</code>

<code>System altered.</code>

<code>Plan hash value: 3579362925</code>

<code>| 1 | </code><code>TABLE</code> <code>ACCESS </code><code>BY</code> <code>INDEX</code> <code>ROWID| T1 | 1 | 5 | 2 (0)| 00:00:01 |</code>

<code>|* 2 | </code><code>INDEX</code> <code>RANGE SCAN | T1_IX_B | 1 | | 1 (0)| 00:00:01 |</code>

是以，直方圖就是專門為了準确評估這種分布不均勻的目标列的可選擇率、結果集的Cardinality而被Oracle引入的，它較長的描述了目标列的資料分布情況，并将這些分布情況記錄在資料字典裡，相當于直覺地告訴了CBO這些列的資料分布情況，于是CBO就能據此來做出相對準确的判斷。

2 直方圖的類型

Oracle資料庫裡的直方圖使用了一種稱為Bucket(桶)的方式來描述目标列的資料分布。這有點類似雜湊演算法的Bucket，它實際上是一個邏輯上的概念，相當于分組，每個Bucket就是一組，每個Bucket裡會存儲一個或多個目标列上的資料。Oracle會用兩個次元來描述一個Bucket，這兩個次元分别是ENDPOINT NUMBER和ENDPOINT VALUE。Oracle會将每個Bucket的次元ENDPOIONTNUMBER和ENDPOINT VALUE記錄在資料字典基表HISTGRM$中，這樣就達到了目标列的直方圖統計資訊記錄在資料字典中的目的。次元ENDPOINT NUMBER和ENDPOINT VALUE分别對應于資料字典DBA_TAB_HISTOGRAMS、DBA_PART_HISTOGRAMS及DBA_SUBPART_HISTOGRAMS中的字段ENDPOINT_NUMBER/BUCKET_NUMBER和ENDPOINT_VALUE。同時，Oracle還會記錄目标列的直方圖統計資訊所占用的Bucket的總數，可以通過資料字典DBA_TAB_COL_STATISTICS、DBA_PART_COL_STATISTICS及DBA_SUBPART_COL_STATISTICS中字段NUM_BUCKETS來檢視目标列對應直方圖的Bucket的總數。

在Oracle 12c之前，Oracle資料庫裡的直方圖分為兩種類型，分别是Frequency和HeightBalanced(Oracle 12c中還存在名為Top-Frequency和Hybrid類型的直方圖)。在Oracle 12以之前，如果存儲在資料字典裡描述目标列直方圖的Buckt的數量等于目标列的distinct值的數量，則這種類型的直方圖就是Frequency類型的直方圖。如果存儲在資料字典裡描述目标列直方圖的Bucket的數量小于目标列的distinct值的數量，則這種類型的直方圖就是Height Balanced類型的直方圖。

2.1 Frequency類型的直方圖

對于Frequency類型的直方圖而言，目标列直方圖的Bucket的數量就等于目标列的distinct的數量，此時目标列有多個個distinct值，Oracle在資料字典DBA_TAB_HISTOGRAMS、DBA-PART_HISTOGRAMS、DBA_SUBPART_HISTOGRAMS中就會存儲多少條記錄，每一條記錄不代表了對其中的一個Bucket的描述，上述資料字典中的字段ENDPOINT_VALUE記錄了這些distinct值，而字段ENDPOINT_NUMBER是一個累加值，實際上，我們可以用一條記錄的ENDPOINT_NUMBER值減去它的上一條記錄的ENDPOINT_NUMBER值來得到這條記錄本身所對應的ENDPOINT_VALUE值的記錄數。

實際上，Frequency類型的直方圖就是把目标列的每一個distinct值都記錄在資料字典裡，同時在資料字典裡記錄記錄每個distinct值在目标表裡一共有多少條記錄，這樣CBO就能非常清楚地知道目标列在目标表裡的實際資料分布情況了。這種Frequency類型的直方圖所對應的收集方法并不适用于目标列的distinct值非常多的情形，是以Oracle對Frequence類型的直方圖有如下限制：Frequency類型的直方圖所對應的Bucket的數量不能超過254(注意，Oracle 12c 中将不再有這一限制，在Oracle 12c中Frequency類型的直方圖所對應的Bucket的數量可以超過254)，即Frequency類型的直方圖隻适用于那些目标列的distinct值數量小于或等于254的情形。

<code>zx@ORCL></code><code>create</code> <code>table</code> <code>h (x number);</code>

<code>zx@ORCL></code><code>declare</code>

<code>i number;</code>

<code>begin</code>

<code>insert</code> <code>into</code> <code>h </code><code>values</code><code>(1);</code>

<code> </code><code>8 </code><code>insert</code> <code>into</code> <code>h </code><code>values</code><code>(3);</code>

<code> </code><code>11 </code><code>insert</code> <code>into</code> <code>h </code><code>values</code><code>(5);</code>

<code> </code><code>14 </code><code>insert</code> <code>into</code> <code>h </code><code>values</code><code>(7);</code>

<code> </code><code>17 </code><code>insert</code> <code>into</code> <code>h </code><code>values</code><code>(10);</code>

<code> </code><code>20 </code><code>insert</code> <code>into</code> <code>h </code><code>values</code><code>(16);</code>

<code> </code><code>23 </code><code>insert</code> <code>into</code> <code>h </code><code>values</code><code>(27);</code>

<code> </code><code>26 </code><code>insert</code> <code>into</code> <code>h </code><code>values</code><code>(32);</code>

<code> </code><code>29 </code><code>insert</code> <code>into</code> <code>h </code><code>values</code><code>(39);</code>

<code> </code><code>32 </code><code>insert</code> <code>into</code> <code>h </code><code>values</code><code>(49);</code>

<code> </code><code>34 </code><code>commit</code><code>;</code>

<code>zx@ORCL></code><code>select</code> <code>count</code><code>(*) </code><code>from</code> <code>h;</code>

<code> </code><code>COUNT</code><code>(*)</code>

按照Frequency類型直方圖的定義，如果對列X收集Frequency類型的直方圖，則DBA_TAB_HISTOGRAMS中應該有10條記錄，而且這10條記錄的ENDPOINT_VALUE記錄的就是這10個distinct值，對應的ENDPOINT_NUMBER就是到此distinct值為止累加的行記錄數。這10條記錄的ENDPOINT_VALUE和ENDPOINT_NUMBER實際上可以用如下SQL的顯示結果來模拟：

<code>zx@ORCL></code><code>select</code> <code>x </code><code>as</code> <code>x,</code><code>count</code><code>(*) </code><code>as</code> <code>cardinality,</code><code>sum</code><code>(</code><code>count</code><code>(*)) over(</code><code>order</code> <code>by</code> <code>x range unbounded preceding) </code><code>as</code> <code>cum_cardinality </code><code>from</code> <code>h </code><code>group</code> <code>by</code> <code>x;</code>

<code> </code><code>X CARDINALITY CUM_CARDINALITY</code>

<code>10 </code><code>rows</code> <code>selected.</code>

上述查詢結果中的列X就模拟了DBA_TAB_HISTOGRAMS中那10條記錄的ENDPOINT_VALUE，列CUM_CARDINALITY就模拟了DBA_TAB_HISTOGRAMS中那10條記錄的ENDPOINT_NUMBER。

對表h的列x來實際收集一下直方圖統計資訊

<code>zx@ORCL></code><code>exec</code> <code>dbms_stats.gather_table_stats(ownname=></code><code>USER</code><code>,tabname=></code><code>'H'</code><code>,method_opt=></code><code>'for columns size auto X'</code><code>,</code><code>cascade</code><code>=></code><code>true</code><code>,estimate_percent=>100);</code>

收集完統計資訊後發現DBA_TAB_COL_STATISTICS中列x所對應的字段HISTOGRAM的值為NONE，這表明現在列x上依然沒有直方圖統計資訊：

<code>zx@ORCL></code><code>select</code> <code>table_name,column_name,num_distinct,density,num_buckets,histogram </code><code>from</code> <code>dba_tab_col_statistics </code><code>where</code> <code>table_name=</code><code>'H'</code><code>;</code>

<code>TABLE_NAME COLUMN_NAM NUM_DISTINCT DENSITY NUM_BUCKETS HISTOGRAM</code>

這種現象是正常的。因為Oracle在自動收集直方圖統計資訊時會秉承一個原則，那就是隻對那些用過的列(即在SQL語句where條件中出現過的列)收集直方圖統計資訊。Oracle會在表SYS.COL_USAGE$中記錄各表中各列的使用情況，在自動收集直方圖統計資訊時Oracle會查詢SYS.COL_USAGE$，如果發現其中沒有目标列的使用記錄，那就不會對目标列收集直方圖統計資訊。表H剛剛建立，還沒有在SQL語句的where條件中使用過列X，是以這裡不會對列X收集直方圖統計資訊。

收集直方圖的前提條件是：1.列上的資料分布不均勻，2.列在sql的where條件中被使用過

<code>zx@ORCL></code><code>select</code> <code>name</code><code>,intcol# </code><code>from</code> <code>sys.col$ </code><code>where</code> <code>obj# = (</code><code>select</code> <code>object_id </code><code>from</code> <code>dba_objects </code><code>where</code> <code>object_name=</code><code>'H'</code><code>);</code>

<code>NAME</code> <code>INTCOL#</code>

<code>zx@ORCL></code><code>select</code> <code>obj#,intcol#,equality_preds </code><code>from</code> <code>sys.col_usage$ </code><code>where</code> <code>obj# = (</code><code>select</code> <code>object_id </code><code>from</code> <code>dba_objects </code><code>where</code> <code>object_name=</code><code>'H'</code><code>);</code>

<code>no</code> <code>rows</code> <code>selected</code>

<code>zx@ORCL></code><code>select</code> <code>count</code><code>(*) </code><code>from</code> <code>h </code><code>where</code> <code>x=10;</code>

<code> </code><code>OBJ# INTCOL# EQUALITY_PREDS</code>

再次對表H的列X自動收集直方圖統計資訊：

<code>H X 10 .0000125 10 FREQUENCY</code>

另外DBA_TAB_COL_STATISTICS中列x所對應的字段HISTORAM的值已經由NONE變成了RREQUENCY，這說明現在列X上已經有了Frequency類型的直方圖

可以從DBA_TAB_HISTOGRAMS中看到列x的Frequence類型的直方圖的具體資訊：

<code>zx@ORCL></code><code>select</code> <code>table_name,column_name,endpoint_number,endpoint_value </code><code>from</code> <code>dba_tab_histograms </code><code>where</code> <code>table_name=</code><code>'H'</code><code>;</code>

<code>TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE</code>

從結果中可以看出，DBA_TAB_HISTOGRAMS中的10條記錄與之前模拟出來的結果一模一樣。

介紹完Frequency類型的直方圖的含義，現在來讨論Oracle資料庫裡針對文本類型字段的直方圖統計的先天缺陷了。

在Oracle資料庫，如果針對文本開的字段收集直方圖統計資訊，則Oracle隻會将該文本字段的文本值的頭32個位元組(Byte)給取出來(實際上隻取頭15個位元組)，并将其轉換成一個浮點數，然後就将這個浮點數作為其直方圖統計資訊存儲在上述資料字典裡。這種處理機制的先天身陷就在于，對于那些超過32個位元組的文本型字段，隻要其對應記錄的文本值的頭32個位元組相同，Oracle在收集直方圖統計資訊時就會認為這引起記錄在該字段的文本值是相同的，即使實際上它們并不相同。這種先天性缺陷會直接影響CBO對相關文本類型字段的可選擇率及傳回結果集的Cardinality的評估。

使用之前的測試表T1，其中列B為文本型字段

<code>zx@ORCL></code><code>select</code> <code>count</code><code>(*) </code><code>from</code> <code>t1 </code><code>where</code> <code>b=</code><code>'1'</code><code>;</code>

<code>T1 B 2 .000049995 2 FREQUENCY</code>

從DBA_TAB_HISTOGRAMS中檢視列B的直方圖具體資訊

從結果可以看到，由文本型的'1'和'2'轉換而來的浮點數。

轉換方法：

select dump('1',16)from dual;

将0x31右邊補0一直補到15個位元組的長度，再将其轉換為十進制數：

<code>zx@ORCL></code><code>select</code> <code>dump(</code><code>'1'</code><code>,16)</code><code>from</code> <code>dual;</code>

<code>zx@ORCL></code><code>select</code> <code>to_number(</code><code>'310000000000000000000000000000'</code><code>,</code><code>'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'</code><code>)</code><code>from</code> <code>dual;</code>

<code>TO_NUMBER(</code><code>'310000000000000000000000000000'</code><code>,</code><code>'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'</code><code>)</code>

轉換出的值與資料字典的資料一緻。

再建立一個測試表T2，有一個長度為33位元組的文本型字段B：

<code>zx@ORCL></code><code>create</code> <code>table</code> <code>t2(b varchar2(33));</code>

<code>zx@ORCL></code><code>insert</code> <code>into</code> <code>t2 </code><code>values</code><code>(</code><code>'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa1'</code><code>);</code>

<code>1 row created.</code>

<code>zx@ORCL></code><code>insert</code> <code>into</code> <code>t2 </code><code>values</code><code>(</code><code>'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa2'</code><code>);</code>

<code>zx@ORCL></code><code>commit</code><code>;</code>

<code>Commit</code> <code>complete.</code>

這三條記錄的頭32個位元組均相同，均為32個a，但distinct值有兩個

<code>zx@ORCL></code><code>select</code> <code>b,length(b)</code><code>from</code> <code>t2;</code>

<code>B LENGTH(B)</code>

<code>aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa1 33</code>

<code>aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa2 33</code>

<code>zx@ORCL></code><code>select</code> <code>count</code><code>(</code><code>distinct</code><code>(b)) </code><code>from</code> <code>t2;</code>

<code>COUNT</code><code>(</code><code>DISTINCT</code><code>(B))</code>

使用一下列B，以讓SYS.COL_USAGE$中有列B的使用記錄：

select count(*) from t2 where b='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa2';

對列B以自動方式收集直方圖：

<code>zx@ORCL></code><code>exec</code> <code>dbms_stats.gather_table_stats(</code><code>USER</code><code>,</code><code>'T2'</code><code>,estimate_percent=>100,method_opt=></code><code>'for columns size auto B'</code><code>);</code>

現在DBA_TAB_COL_STATISTICS中列B所對應的字段HISTOGRAM的值為FREQUENCY(注意：10.2.0.4和11.2.0.1為FREQUENCY,11.2.0.4為HEIGHT BALANCED)，說明現在列B上已經有了Frequency類型的直方圖統計資訊：

<code>SQL> </code><code>select</code> <code>table_name,column_name,num_distinct,density,num_buckets,histogram </code><code>from</code> <code>dba_tab_col_statistics </code><code>where</code> <code>table_name=</code><code>'T2'</code><code>;</code>

<code>T2 B 1 .166666667 1 FREQUENCY</code>

注意，上述查詢結果中文本型字段B的不同distinct的值隻有1個，Frequency類型的直方圖所在的Bucket數量也隻有1個，這明顯和事實不符。其實這已經說明了對那些超過32位元組的文本型字段而言，隻要對應記錄的文本值的頭32個位元組相同，Oracle在收集直方圖統計資訊時就會認為這些記錄在該字段的文本值是相同的，即使實際上它們并不相同。

從DBA_TAB_HISTOGRAMS中看到列B的Frequency類型的直方圖統計資訊的具體内容，這進一步證明了上述結論：

<code>SQL> </code><code>select</code> <code>table_name,column_name,endpoint_number,endpoint_value </code><code>from</code> <code>dba_tab_histograms </code><code>where</code> <code>table_name=</code><code>'T2'</code><code>;</code>

<code>SQL> </code><code>select</code> <code>dump(</code><code>'a'</code><code>,</code><code>'16'</code><code>) </code><code>from</code> <code>dual;</code>

<code>SQL> </code><code>select</code> <code>to_number(</code><code>'616161616161616161616161616161'</code><code>,</code><code>'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'</code><code>) </code><code>from</code> <code>dual;</code>

<code>TO_NUMBER(</code><code>'616161616161616161616161616161'</code><code>,</code><code>'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'</code><code>)</code>

通過計算互相符合。

對表T2執行如下sql

select count(*) fromt2 where b='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa1';

實際傳回結果集的Cardinality為1

但從執行計劃的結果可以看出CBO錯誤地評估出上述SQL傳回結果集的Cardinality為3：

<code>SQL> </code><code>select</code> <code>count</code><code>(*) </code><code>from</code> <code>t2 </code><code>where</code> <code>b=</code><code>'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa1'</code><code>;</code>

<code>SQL> </code><code>select</code> <code>* </code><code>from</code> <code>table</code><code>(dbms_xplan.display_cursor(</code><code>null</code><code>,</code><code>null</code><code>,</code><code>'all'</code><code>));</code>

<code>SQL_ID 3n69wfhjuj4sg, child number 0</code>

<code>select</code> <code>count</code><code>(*) </code><code>from</code> <code>t2 </code><code>where</code> <code>b=</code><code>'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa1'</code>

<code>Plan hash value: 3321871023</code>

<code>| 1 | SORT AGGREGATE | | 1 | 34 | | |</code>

<code>|* 2 | </code><code>TABLE</code> <code>ACCESS </code><code>FULL</code><code>| T2 | 3 | 102 | 3 (0)| 00:00:01 |</code>

這是因為DBA_TAB_HISTOGRAMS中列B的Frequency類型的直方圖隻有1個Bucket，這會使Oracle認為表T2中隻有一個distinct文本值32個'a'，是以對于上述SQL而言，Oracle會認為該SQL要通路的就是表T2的所有資料。

2.2 Height Balanced類型的直方圖

前面介紹到Oracle 12c之前，Frequence類型的直方圖對應的Bucket的數量不能超過254，那如果目标列的distinct值的數量大于254呢？此時Oracle會對目标列收集Height Balanced類型的直方圖。

<code>zx@ORCL></code><code>create</code> <code>table</code> <code>t1(id number);</code>

<code>zx@ORCL></code><code>begin</code>

<code> </code><code>4 </code><code>insert</code> <code>into</code> <code>t1 </code><code>values</code><code>(i);</code>

<code> </code><code>7 </code><code>commit</code><code>;</code>

<code>#</code><code>distinct</code><code>值的數量為254</code>

<code>zx@ORCL></code><code>select</code> <code>count</code><code>(</code><code>distinct</code><code>(id)) </code><code>from</code> <code>t1;</code>

<code>COUNT</code><code>(</code><code>DISTINCT</code><code>(ID))</code>

<code>#執行一個查詢使id列在</code><code>where</code><code>條件中</code>

<code>zx@ORCL></code><code>select</code> <code>* </code><code>from</code> <code>t1 </code><code>where</code> <code>id=1;</code>

<code>#收集直方圖資訊</code>

<code>zx@ORCL></code><code>exec</code> <code>dbms_stats.gather_table_stats(ownname=></code><code>USER</code><code>,tabname=></code><code>'T1'</code><code>,method_opt=></code><code>'for columns size auto id'</code><code>,</code><code>cascade</code><code>=></code><code>true</code><code>,estimate_percent=>100);</code>

<code>zx@ORCL>col table_name </code><code>for</code> <code>a10</code>

<code>zx@ORCL>col column_name </code><code>for</code> <code>a10</code>

<code>zx@ORCL></code><code>set</code> <code>linesize 200</code>

<code>T1 ID 254 .000015372 251 FREQUENCY</code>

<code>zx@ORCL></code><code>select</code> <code>endpoint_value,endpoint_number </code><code>from</code> <code>dba_tab_histograms </code><code>where</code> <code>owner=</code><code>user</code> <code>and</code> <code>table_name=</code><code>'T1'</code><code>;</code>

<code>ENDPOINT_VALUE ENDPOINT_NUMBER</code>

<code>254 </code><code>rows</code> <code>selected.</code>

從輸出的結果可以看出ID列上已經有了Frequency類型的直方圖。

現在對表T1再插入一條包含不同ID值的記錄，然後删除列ID上的直方圖資訊，再列ID列重新收集直方圖資訊，然後查詢ID列直方圖的類型。

<code>zx@ORCL></code><code>insert</code> <code>into</code> <code>t1 </code><code>values</code><code>(255);</code>

<code>zx@ORCL></code><code>select</code> <code>count</code><code>(</code><code>distinct</code> <code>id) </code><code>from</code> <code>t1;</code>

<code>COUNT</code><code>(DISTINCTID)</code>

<code>zx@ORCL></code><code>exec</code> <code>dbms_stats.gather_table_stats(ownname=></code><code>USER</code><code>,tabname=></code><code>'T1'</code><code>,method_opt=></code><code>'for columns size 1 id'</code><code>,</code><code>cascade</code><code>=></code><code>true</code><code>,estimate_percent=>100);</code>

<code>T1 ID 255 .004243247 254 HEIGHT BALANCED</code>

從輸出的結果看現在ID列上的直方圖類型已經從之前的Frequency變為了Height Balanced。

對于Height Balanced類型的直方圖而言，即當目标列直方圖的Bucket的數量小于目标列的distinct值的數量時，Oracle首先會根據目标列對目标表的所有記錄按從小到大的順序排序，然後用目标表總的記錄數除以需要使用的Bucket的數量，來決定每個Bucket裡需要描述的已經排好序的記錄數。假設目标表的總記錄數為M，需要使用的Bucket數量為N，每個Bucket裡需要描述的已經排好序的記錄數為O，則O=M/N；

然後Oracle會用DBA_TAB_HISTOGRAMS、DBA_PART_HISTOGRAMS、DBA_SUBPART_HISTOGRAMS中的每一條記錄的ENDPOINT_NUMBER來記錄Bucket号，Bucket号從0開始，一直到N。其中0号Bucket裡存儲的是目标列的最小值，是以0号Bucket所在記錄的ENDPOINT_NUMBER值為0，其餘Bucket所在記錄的ENDPOINT_NUMBER從1一直遞增到N，這些記錄除了0号Bucket所在記錄的ENDPOINT_VALUE值是目标列的最小值外，其他所有記錄的ENDPOINT_VALUE值實際上存儲的是到此記錄所描述述Bucket為此之前所有Bucket描述的記錄裡目标列的最大值。即除了0号Bucket之外，其他所有記錄的ENDPOINT_VALUE值都是用如下公式來計算的：

我們再來使用之前的H表來說明Height Balanced類型的直方圖

先删除表H中已存在的Frequency類型的直方圖

<code>zx@ORCL></code><code>exec</code> <code>dbms_stats.gather_table_stats(ownname=></code><code>USER</code><code>,tabname=></code><code>'H'</code><code>,method_opt=></code><code>'for columns size 1 X'</code><code>,</code><code>cascade</code><code>=></code><code>true</code><code>,estimate_percent=>100);</code>

對于Height Balanced類型的直方圖而言，目标列直方圖的Bucket的數量會小于目标列的distinct值的數量。這裡表H有10個distinct值，如果在收集直方圖統計資訊的時候指定Bucket數量為5，則Oracle就應該收集Height Balanced類型的直方圖了。這裡收集直方圖統計資訊時指定method_opt的值為'for columns size 5 X',這裡表示在對列X收集直方圖時已經指定所用Bucket的數量為5(注意，這裡的Bucket數量不含0号Bucket)：

<code>zx@ORCL></code><code>exec</code> <code>dbms_stats.gather_table_stats(ownname=></code><code>USER</code><code>,tabname=></code><code>'H'</code><code>,method_opt=></code><code>'for columns size 5 X'</code><code>,</code><code>cascade</code><code>=></code><code>true</code><code>,estimate_percent=>100);</code>

<code>H X 10 .085276318 5 HEIGHT BALANCED</code>

從輸出來看X列所對應的字段HISTOGRAM的值為HEIGHT BALANCED，這說明X列上已經有Height Balanced類型的直方圖。

現在按照剛才介紹的算法算一下DBA_TAB_HISTOGRAM中存儲的Height Balanced類型的直方圖統計資訊的詳情。

現在需要使用的Bucket數量為5(不含0号Bucket)表H中總的記錄數為40000，是以每個Bucket裡所需要描述的記錄數為40000/5=8000。

0号Bucket所在記錄的ENDPOINT_NUMBER是0，ENDPOINT_VALUE是表H中10個distinct值中最小值1。

使用如下公式計算出每個Bucket所在記錄的ENDPOINT_VALUE值：

<code>#Bucket1</code>

<code>zx@ORCL></code><code>select</code> <code>max</code><code>(x) </code><code>from</code> <code>(</code><code>select</code> <code>x </code><code>from</code> <code>h </code><code>order</code> <code>by</code> <code>x) </code><code>where</code> <code>rownum<8000;</code>

<code>#Bucket2</code>

<code>#Bucket3</code>

<code>#Bucket4</code>

<code>#Bucket5</code>

從上述結果可以看到2号Bucket和3号Bucket所對應記錄的ENDPOINT_VALUE值都是10，是以Oracle會将2号和3号Bucket合并存儲，合并後的記錄ENDPOINT_NUMBER值是3，ENDPOINT_VALUE值是10。這裡10就是一個popular value。經過上述分析，我們可知DBA_TAB_HISTOGRAMS中的存儲的Height Balanced類型的直方圖統計資訊的詳細應為如下所示：

<code>ENDPOINT_NUMBER ENDPOINT_VALUE</code>

我們查詢DBA_TAB_HISTOGRAMS中列X的Height Balanced類型的直方圖統計資訊：

可以看到實際查詢結果與我們分析的一緻。

3 直方圖的收集方法

在Oracle資料庫裡收集直方圖統計資訊，通常是在調用DBMS_STATS包中的存儲過程GATHER_DATABASE_STATS/GATHER_DICTIONARY_STATS/GATHER_SCHEMA_STAS/GATHER_TABLE_STATS收集統計資訊時通過指定輸入參數METHOD_OPT來實作。當然也可以使用ANALYZE指令來收集直方圖統計資訊，比如使用指令“analyze table h compute statistics forcolumns X”來收集表H的列X的直方圖統計資訊。因為ANALYZE指令在收集統計資訊方面有先天的缺陷，是以這裡隻讨論用DBMS_STATS包來收集直方圖統計資訊。

DBMS_STATS包中上述存儲過程的輸入參數METHOD_OPT可以接受如下的輸入值：

FOR ALL[INDEXES|HIDDEN] COLUMNS [size_clause]

FOR COLUMNS[size_clause] column|attribute [size_clause] [,column|attribute[size_clause]...]

其中的size_clause必須符合如下的格式：

SIZE {integer|REPEAT|AUTO|SKEWONLY}

size_clause子名中各選項的含義如下所述：

Integer：直方圖的Bucket的數量，必須是在1~254的範圍内，1表示删除該目标列上直方圖統計資訊。

REPEAT：隻對已經有直方圖統計資訊的列收集直集直方圖統計資訊。

AUTO：讓Oracle自行決定是否對目标列收集直方圖統計資訊，以及使用哪種類型的直方圖。

SKEWONLY：隻對資料分布不均衡的列收集直方圖統計資訊。

使用SCOTT使用者下的表EMP為例來說明：

<code>scott@ORCL> </code><code>desc</code> <code>emp</code>

<code> </code><code>EMPNO </code><code>NOT</code> <code>NULL</code> <code>NUMBER(4)</code>

<code> </code><code>ENAME VARCHAR2(10)</code>

<code> </code><code>JOB VARCHAR2(9)</code>

<code> </code><code>MGR NUMBER(4)</code>

<code> </code><code>HIREDATE </code><code>DATE</code>

<code> </code><code>SAL NUMBER(7,2)</code>

<code> </code><code>COMM NUMBER(7,2)</code>

<code> </code><code>DEPTNO NUMBER(2)</code>

1)對表EMP所有有索引的列以自動收集的方式收集直方圖統計資訊：

exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMP',method_opt=>'for all indexed columns size auto');

2)對表EMP上的列EMPNO和DEPTNO以自動收集的方式收集直方圖統計資訊：

exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMP',method_opt=>'for columns size auto EMPNO DEPTNO');

3)對表EMP上的列EMPNO和DEPTNO收集直方圖統計資訊，同時指定Bucket數量均為10：

exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMP',method_opt=>'for columns size 10 EMPNO DEPTNO');

4)對表EMP上的列EMPNO和DEPTNO收集直方圖統計資訊，同時指定列EMPNO的Bucket數量為10，列DEPTNO的Bucket數量為5：

exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMP',method_opt=>'for columns EMPNO size 10 DEPTNO size 5');

5)隻删除表EMP上列EMPNO的直方圖統計資訊：

execdbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMP',method_opt=>'for columns EMPNO size 1');

6)删除表EMP上所有列的直方圖統計資訊：

exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMP',method_opt=>'for all columns size 1');

參考《基于Oracle的SQL優化》

<a href="http://docs.oracle.com/cd/E11882_01/server.112/e41573/stats.htm#PFGRF003" target="_blank">http://docs.oracle.com/cd/E11882_01/server.112/e41573/stats.htm#PFGRF003</a>

本文轉自hbxztc 51CTO部落格，原文連結：http://blog.51cto.com/hbxztc/1900721，如需轉載請自行聯系原作者

Oracle的直方圖

繼續閱讀

阿裡雲天池龍珠計劃SQL訓練營day1

賦予使用者dba權限--解釋以上：

實驗樓sql進階之成績管理系統的資料操作(window)

轉詳解C#資料庫存取圖檔三大方式

Oracle的基本操作

為什麼要選擇UniDAC

BMP檔案結構及圖像每行位元組計算方法

磁盤結構及在Linux中的命名

SQL優化SQL語句優化的目的

JAVA高效程式設計指南

windows不能在本地計算機上運作oracleDbConsoleorcl

關于SQL語言

SQL語言基礎：常用的資料查詢語句

neo4j之cypher使用文檔

sqlServer根據經緯查距離

Oracle 批量查詢傳入List 傳回List