直方图(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选择全表扫描，可以看出上面的两条语句根据实际情况生成了非常完美的执行计划。

ITPub博客

直方图(histograms)

最新文章