直方圖學習

參考文件：
https://blog.csdn.net/javacoffe/article/details/5578206
https://blog.csdn.net/tchic/article/details/52434006
http://www.cnblogs.com/polestar/p/4330968.html
%E7%9B%B4%E6%96%B9%E5%9B%BE%E7%90%86%E8%A7%A3%E4%B8%8E%E5%AE%9E%E9%AA%8C.html
   
     
直方圖學習


一.何為直方圖
　　直方圖是一種幾何形圖表，它是根據從生產過程中收集來的質量資料分佈情況，畫成以組距為底邊、以頻數為高度的一系列連線起來的直方型矩形圖，如圖所示


二.ORACLE 直方圖
　　在Oracle中直方圖是一種對資料分佈情況進行描述的工具。它會按照某一列不同值出現數量多少，以及出現的頻率高低來繪製資料的分佈情況，以便能夠指導最佳化器根據資料的分佈做出正確的選擇。在某些情況下，表的列中的數值分佈將會影響最佳化器使用索引還是執行全表掃描的決策。


　　直方圖的使用不受索引的限制，可以在表的任何列上構建直方圖。構造直方圖最主要的原因就是幫助最佳化器在表中資料嚴重偏斜時做出更好的規劃。如：一到兩個值（status=0和status=1，其中=0有100條資料，=1有1000000條資料，只有這兩個值）就構成了表中的大部分資料（資料傾斜），相關查詢就可能無法幫助減少滿足查詢所需的I/O數量（如查詢status=1）。建立直方圖可以讓基於成本的最佳化器知道何時使用索引才最合適，或何時根據where子句中的值返回表中的80%的記錄。


三.直方圖分類及原理
等頻直方圖：針對包含很少不同值的資料集，就是資料分佈很均勻。
等高直方圖：針對包含很多不同值的資料集，資料分佈不均勻。


　　直方圖資訊的準確性由兩個數值決定，一個是bucket的個數，一個是num_distinct的個數。一般來說，bucket的數量越多，關於列資料分佈的資訊就越準確，但統計直方圖花費的時間就越多，oracle中bucket的最大為254個，預設是75個。而sql server中預設是200個。


當BUCTET < 表的NUM_DISTINCT值得到的是HEIGHT BALANCED 等高直方圖
當BUCTET > 表的NUM_DISTINCT值的時候得到的是FREQUENCY 等頻直方圖
　　由於滿足BUCTET = 表的NUM_DISTINCT值機率較低，所以在Oracle中生成的直方圖大部分是HEIGHT BALANCED（高度平衡）直方圖。




四.Oracle中使用直方圖的場合
在分析表或索引時，直方圖用於記錄資料的分佈。透過獲得該資訊，基於成本的優 化器就可以決定使用將返回少量行的索引，而避免使用基於限制條件返回許多行的索引。直方圖的使用不受索引的限制，可以在表的任何列上構建直方圖。
構造直方圖最主要的原因就是幫助最佳化器在表中資料嚴重偏斜時做出更好的規劃：例如，如果一到兩個值構成了表中的大部分資料(資料偏斜)，相關的索引就可能無法幫助減少滿足查詢所需的I/O數量。建立直方圖可以讓基於成本的最佳化器知道何時使用索引才最合適，或何時應該根據WHERE子句中的值返回表中80％的記錄。
通常情況下在以下場合中建議使用直方圖：
（1）、當Where子句引用了列值分佈存在明顯偏差的列時：當這種偏差相當明顯時，以至於 WHERE 子句中的值將會使最佳化器選擇不同的執行計劃。這時應該使用直方圖來幫助最佳化器來修正執行路徑。（注意：如果查詢不引用該列，則建立直方圖沒有意義。這種錯誤很常見，許多 DBA 會在偏差列上建立柱狀圖，即使沒有任何查詢引用該列。）


（2）、當列值導致不正確的判斷時：這種情況通常會發生在多表連線時，例如，假設我們有一個五項的表聯接，其結果集只有 10 行。Oracle 將會以一種使第一個聯接的結果集（集合基數）儘可能小的方式將表聯接起來。透過在中間結果集中攜帶更少的負載，查詢將會執行得更快。為了使中間結果最小化，最佳化器嘗試在 SQL 執行的分析階段評估每個結果集的集合基數。在偏差的列上擁有直方圖將會極大地幫助最佳化器作出正確的決策。如最佳化器對中間結果集的大小作出不正確的判斷，它可能會選擇一種未達到最最佳化的表聯接方法。因此向該列新增直方圖經常會向最佳化器提供使用最佳聯接方法所需的資訊


五.建立直方圖
Oracle 透過指定 dbms_stats. gather_table_stats 的 method_opt 引數，來建立直方圖的。 method_opt引數說明


==========================================================================================================
Accepts either of the following options, or both in combination:


FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
FOR COLUMNS [size clause] column [size_clause] [,column [size_clause]...]
size_clause is defined as size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}


column is defined as column := column_name | extension name | extension


- integer : Number of histogram buckets. Must be in the range [1,254].
- REPEAT : Collects histograms only on the columns that already have histograms


- AUTO : Oracle determines the columns on which to collect histograms based on data distribution and the workload of the columns.
- SKEWONLY : Oracle determines the columns on which to collect histograms based on the data distribution of the columns.
- column_name : Name of a column
- extension : can be either a column group in the format of (column_name, Colume_name [, ...]) or an expression


The default is FOR ALL COLUMNS SIZE AUTO.
==========================================================================================================


Examples
An extension can be either a column group (see Example 1) or an expression (see Example 2).


Example 1  資料庫自動收集
DBMS_STATS.GATHER_TABLE_STATS('SH', 'SALES', method_opt => 'FOR COLUMNS (empno, deptno)'); 
DBMS_stats.gather_table_stats(cascade => TRUE,degree => 2,estimate_percent => 100,force => TRUE,ownname => USER,tabname => 'T_XFF');
--在gather_table_stats方法中，預設的method_opt值為：FOR ALL COLUMNS SIZE AUTO，所以也是會收集直方圖的統計資訊（和oracle版本相關）
    
Example 2
DBMS_STATS.GATHER_TABLE_STATS('SH', 'SALES', method_opt => 'FOR COLUMNS (sal+comm)');


Example 3
DBMS_STATS.GATHER_TABLE_STATS('SH', 'SALES', method_opt => 'FOR COLUMNS sal size 1');
注：1表示1個bucket的數量，即不做直方圖處理,禁止產生直方圖（size 1）


Example 4
DBMS_STATS.GATHER_TABLE_STATS('SH', 'SALES', method_opt => 'FOR COLUMNS (sal+comm) size 10');
注：把sal，comm兩列，按(sal+comm)表示式的和分成10個bucket。


Example 5
DBMS_STATS.GATHER_TABLE_STATS('SH', 'SALES', method_opt => 'FOR COLUMNS (sal，comm) size 10');


Example 6










--檢視直方圖資訊
SQL> col COLUMN_NAME format a20
SQL> col TABLE_NAME format a15
SQL> SELECT * FROM user_histograms WHERE table_name='T_XFF' AND column_name='OBJECT_ID';
TABLE COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
----- ---------- --------------- -------------- ----------
T_XFF OBJECT_ID            1          2
T_XFF OBJECT_ID            2          3
……
T_XFF OBJECT_ID            73205          76789
T_XFF OBJECT_ID            73206          76800


--檢視直方圖型別 
SQL>   SELECT COLUMN_NAME,HISTOGRAM FROM USER_TAB_COLS WHERE TABLE_NAME='T_XFF' AND column_name='OBJECT_ID';
  
COLUMN_NAME                    HISTOGRAM
------------------------------ ---------------
OBJECT_ID                      FREQUENCY




--注：ENDPOINT_NUMBER列值是bucket（桶）的標識號。如只有0、1兩個bucket說明沒有產生直方圖資訊。
--等頻直方圖，EDNPOINT_NUMBER是累計值，EDNPOINT_NUMBER(N)-EDNPOINT_NUMBER(N-1)=當前桶的資料個數，EDNPOINT_VALUE是列的值。它為每一個列值分配了一個桶。


--在等高直方圖中，EDNPOINT_NUMBER代表桶號,這一點與等頻直方圖不同。


--等頻直方圖比等高直方圖穩定、精確。
--列的唯一值是超過254的。只能使用等高直方圖了。
--在沒有直方圖的情況下，在B列上進行等值查詢的時候，都是索引範圍掃描（即使返回總資料量的80%）
---檢視執行計劃
SQL> set autot trace exp stat
SQL> select object_name from t_xff where object_id=100;