ORACLE等频直方图

kdupapqabp

ORACLE等频直方图
ORACLE直方图是Oracle CBO优化器使用的一种统计数据，比如有一个字段a，取值范围是1－10000，整个表有100万条记录，那么如果你要查询a>10 and a<100的记录，如果这样的记录有100条，那么走索引是最好的，如果这样的记录有90万条，那么走索引肯定不如全表扫描。直方图里面可以看出记录的分布情况，比如1－100有多少条，101－200有多少条记录，等等。优化器通过使用直方图，可以更准确的判断使用什么执行计划最优。
显然对于存在高度不均匀数据的表，使用柱状图能够产生更好的选择性评估，从而产生更加优化的执行计划。柱状图提供一种有效和简捷的方法来呈现数据的分布情况。
oracle统计信息和直方图的理解
以前一直对统计信息的理解就是对行的数据分布的，提供改CBO来选择高效的执行计划。这段时间看了不少资料，对统计有了一个更清晰的认识
统计信息：
1，表中的统计信息
2，索引列的统计信息
3，一般列的统计信息
表的统计信息：1，表行数，使用的块数，空的块数，块的使用率，行迁移和链接的数量，pctfree，pctused的数据，行的平均大小
索引列的统计信息 1，索引的深度（B-Tree的级别），索引叶级的块数量，集群因子（clustering_factor), 唯一值的个数
列的统计信息 1,唯一的值个数，列最大小值，密度（选择率），数据分布（直方图信息），NUll值个数
关于ORACLE直方图的一个简单测试
1、建表
create table tab(a number,b number);
2、插入数据
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'ZFTANG',
TABNAME => 'TAB',
CASCADE => TRUE,
METHOD_OPT => 'FOR COLUMNS B SIZE 1 ');
END;
/
3、建索引
create index ix_tab_b on tab(b);

4、分析表-不产生直方图
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'ZFTANG',
TABNAME => 'TAB',
CASCADE => TRUE,
METHOD_OPT => 'FOR ALL COLUMNS B SIZE 1 ');
END;
/
5、查看结果
select * from USER_TAB_HISTOGRAMS where column_name = 'B';


查看视图USER_TAB_HISTOGRAMS，列B上只有最大值，最小值两条记录分别对应端点号（endpoint_number）0和1，这种显示说明列B没有直方图信息。

6、执行没有直方图之前查询
在没有直方图的情况下，在B列上进行等值查询的时候，都是索引范围扫描。
SQL> select * from tab where b=1;
执行计划
----------------------------------------------------------
Plan hash value: 439197569

----------------------------------------------------------------------------------------
| Id| Operation       | Name | Rows| Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT    |    |1000 |6000 | 4 (0)| 00:00:01 |
| 1 |TABLE ACCESS BY INDEX ROWID| TAB  |1000 |6000 | 4 (0)| 00:00:01 |
|*2 | INDEX RANGE SCAN    | IX_TAB_B |1000 |   | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------


SQL> select * from tab where b=5;

已选择9991行。


执行计划
----------------------------------------------------------
Plan hash value: 439197569

----------------------------------------------------------------------------------------
| Id| Operation       | Name | Rows| Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT    |    |1000 |6000 | 4 (0)| 00:00:01 |
| 1 |TABLE ACCESS BY INDEX ROWID| TAB  |1000 |6000 | 4 (0)| 00:00:01 |
|*2 | INDEX RANGE SCAN    | IX_TAB_B |1000 |   | 2 (0)| 00:00:01 |

7、产生直方图
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'ZFTANG',
TABNAME => 'TAB',
CASCADE => TRUE,
METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO ');
END;
/
收集直方图信息。看看是什么效果。由于列B唯一值的个数没有超过254因此产生的是等频直方图。

8、执行查询结果
在B=1时候采用索引扫描，而B=5时候，已经采用全表扫描了，说明直方图起了作用。

SQL> select * from tab where b=1;
执行计划
----------------------------------------------------------
Plan hash value: 439197569

----------------------------------------------------------------------------------------
| Id| Operation       | Name | Rows| Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT    |    | 1 | 6 | 2 (0)| 00:00:01 |
| 1 |TABLE ACCESS BY INDEX ROWID| TAB  | 1 | 6 | 2 (0)| 00:00:01 |
|*2 | INDEX RANGE SCAN    | IX_TAB_B | 1 |   | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

SQL> select * from tab where b=5;

已选择9991行。


执行计划
----------------------------------------------------------
Plan hash value: 1995730731

--------------------------------------------------------------------------
| Id| Operation   | Name | Rows| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT|  |9991 | 59946 | 6 (0)| 00:00:01 |
|*1 |TABLE ACCESS FULL| TAB|9991 | 59946 | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------