直方圖(histogram)本來是統計學概念，為什麼資料庫引入此概念，究其原因是因為透過直方圖最佳化器可以生成較好的執行計劃。
一旦對列建立了直方圖後，透過直方圖可以看到列值各個資料出現的頻率（注意這裡說的是頻率，並不一定是某數值出現的具體次數）。直方圖分為兩種型別：頻度直方圖以及高度平衡直方圖。頻度直方圖針對列有少量的不同值比較有效，當列中不同值的數目較大，建議使用高度平衡直方圖。Oracle會根據列中不同值的數量來自動判斷所要建立的直方圖型別。
依然拿上篇部落格的表為例子(http://blog.itpub.net/29827284/viewspace-2134398/)

SQL> exec dbms_stats.gather_table_stats(USER,'T',method_opt=>'for columns skew size 11');
SQL> select * from user_tab_histograms t where t.table_name='T' and COLUMN_NAME='SKEW';
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALUE
------------- ------------- --------------- -------------- ------------------------------
T SKEW 1 1
T SKEW 2 2
T SKEW 3 3
T SKEW 4 4
T SKEW 5 5
T SKEW 6 6
T SKEW 7 7
T SKEW 8 8
T SKEW 9 9
T SKEW 10 10
T SKEW 10000 10000

ENDPONIT_VALUE顯示的是列值
ENDPOINT_NUMBER顯示的是累積的行數更嚴格的說是累積的頻率。
為了計算一個特定列值的頻率要使用與它相關的endpoint_number值減去它之前的累計值。對於列SKEW中ENDPOINT_VALUE為10000，它對應的ENDPOINT_NUMBER=10000，上一個ENDPOINT_VALUE為10的ENDPOINT_NUMBER=10，那麼說明ENDPOINT_VALUE為10000有10000-10=9990條記錄。有了直方圖，可以讓最佳化器做出最優的執行計劃。廢話少說，透過實驗說明：

點選(此處)摺疊或開啟

SQL> explain plan for select * from t where skew=1;
Explained.
SQL> SELECT * FROM table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 7 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1 | 7 | 7 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SKEW"=1)

點選(此處)摺疊或開啟

SQL> explain plan for select * from t where skew=10000;
SQL> SELECT * FROM table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9990 | 69930 | 7 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 9990 | 69930 | 7 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SKEW"=10000)

可以看出收集資訊建立了直方圖後，評估出的基數相對準確了很多，從而避免生成錯誤的執行計劃。為了更好的說明情況，可以在SKEW列建立索引

點選(此處)摺疊或開啟

SQL> create index ind_skew on t(skew);
Index created.
SQL> explain plan for select * from t where skew=1;
Explained.
SQL> SELECT * FROM table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3288809695
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 7 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_SKEW | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SKEW"=1)
14 rows selected.
SQL> explain plan for select * from t where skew=10000;
Explained.
SQL> SELECT * FROM table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9990 | 69930 | 7 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 9990 | 69930 | 7 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SKEW"=10000)

WHERE條件SKEW=1選擇走索引，WHERE條件SKEW=10000因為返回條目佔據了表的絕多部分記錄，Oracle選擇全表掃描，可以看出上面的兩條語句根據實際情況生成了非常完美的執行計劃。

直方圖(histograms)

相關文章