ORACLE等频直方图

 

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 |

转载：http://blog.csdn.net/zftang/article/details/6364863