SQL> create table tab(a number,b number);
Table created.
SQL> insert into tab select rownum,rownum from dual connect by level <=10000;10000 rows created.
SQL> commit;
Commit complete.
SQL> update tab set b=5 where b between 6 and 9995;
9990 rows updated.
SQL> commit;
Commit complete.
SQL> create index tab_b_idx on tab(b);
Index created.
然后分析表,强制使列B不产生直方图。
SQL> exec dbms_stats.gather_table_stats('HR','TAB',cascade=>true,method_opt=>'FOR COLUMNS B SIZE 1');PL/SQL procedure successfully completed.
查看视图USER_TAB_HISTOGRAMS 或者DBA_TAB_COL_STATISTICS
SQL> select TABLE_NAME,COLUMN_NAME,ENDPOINT_NUMBER,ENDPOINT_VALUE from user_histograms where TABLE_NAME='TAB'TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
------------ ------------ --------------- --------------
TAB B 0 1
TAB B 1 10000
列B上只有最大值,最小值两条记录分别对应端点号(endpoint_number)0和1,这种显示说明列B没有直方图信息。
在没有直方图的情况下,在B列上进行等值查询的时候,都是索引范围扫描。
SQL> select * from tab where b=5Execution Plan
----------------------------------------------------------
Plan hash value: 157166354-----------------------------------------------------------------------------------------
| 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 | TAB_B_IDX | 1000 | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 2 - access("B"=5)
收集直方图信息。看看是什么效果。由于列B唯一值的个数没有超过254因此产生的是等频直方图。
SQL> exec dbms_stats.gather_table_stats('HR','TAB',cascade=>true);PL/SQL procedure successfully completed.
在B=1时候采用索引扫描,而B=5时候,已经采用全表扫描了,说明直方图起了作用。
SQL> select * from tab where b=1;Execution Plan
----------------------------------------------------------
Plan hash value: 157166354-----------------------------------------------------------------------------------------
| 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 | TAB_B_IDX | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 2 - access("B"=1)
SQL> select * from tab where b=5;
Execution Plan
----------------------------------------------------------
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 |
--------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("B"=5)
查看此时的直方图信息:
SQL> select TABLE_NAME,COLUMN_NAME,ENDPOINT_NUMBER,ENDPOINT_VALUE from user_histograms where table_name='TAB';
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
------------ ------------ --------------- --------------
TAB B 1 1
TAB B 2 2
TAB B 3 3
TAB B 4 4
TAB B 9995 5
TAB B 9996 9996
TAB B 9997 9997
TAB B 9998 9998
TAB B 9999 9999
TAB B 10000 10000
TAB A 0 1TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
------------ ------------ --------------- --------------
TAB A 1 10000
12 rows selected.
其中EDNPOINT_NUMBER是累计值。EDNPOINT_VALUE是列的值。可以看出这种等频直方图统计的列的信息是非常精确的。它为每一个列值分配了一个bucket。从执行计划的ROWS部分也可以看出ORACLE计算出来的cardinality是9991,和实际的情况完全吻合。
如果想知道每一个列值对应的数量是多少,需要做一下简单的减法运算:假如想知道列值等于5的个数,那么可以通过:9995-4=9991得到。这就是ENDPOINT_NUMBER累计值的含义。
等高直方图,当列上的数据不同值超过254时,Oracle将会默认将列上的数据划分为高度一致但频度不一致的等高直方图。
SQL> exec dbms_stats.gather_table_stats('HR','TAB',cascade=>true,method_opt=>'FOR COLUMNS B SIZE 8);PL/SQL procedure successfully completed.
由于列B有10个唯一值,通过上面的size 8可以强制ORACLE使用等高直方图。
查看直方图信息.
SQL> select TABLE_NAME,COLUMN_NAME,ENDPOINT_NUMBER,ENDPOINT_VALUE from user_histograms where table_name='TAB';TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
------------ ------------ --------------- --------------
TAB B 0 1
TAB B 7 5
TAB B 8 10000
TAB A 0 1
TAB A 1 10000从查询结果惊奇的发现只有三个桶0 7 8,原来ORACLE会自动省去EDNPOINT_VALUE值相同且ENDPOINT_NUMBER相邻的桶的值。省去了桶(EDNPOINT_NUMBER)为1 2 3 4 5 6 ,EDNPOINT_VALUE为5的六条内容。
说明:在等高直方图中,EDNPOINT_NUMBER代表桶号,这一点与等频直方图不同。再看等高直方图下的执行计划:
SQL> select * from tab where b=5Execution Plan
----------------------------------------------------------
Plan hash value: 1995730731--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9982 | 59892 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TAB | 9982 | 59892 | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 1 - filter("B"=5)
有执行计划的ROWS部分,ORACLE计算出来的cardinality不是特别精确的。9991才是精确值。而等频直方图可以精确到9991,因此可以说等频直方图比等高直方图稳定,精确。
可是现实很多时候,列的唯一值是超过254的。只能使用等高直方图了。
一个注意点 如果需要删除直方图信息,10g中可以通过上面提到的
exec dbms_stats.gather_table_stats('HR','TAB',cascade=>true,method_opt=>'FOR COLUMNS B SIZE 1');
但这却得再次手机表的统计信息,十分不合理,11g有如下方法可以直接删除直方图信息
dbms_stats.delete_column_stats(ownname => user,
tabname => 'T',
colname => 'VAL',
col_stat_type => 'HISTOGRAM')