加载中…
个人资料
Alls_Z
Alls_Z
  • 博客等级:
  • 博客积分:0
  • 博客访问:10,359
  • 关注人气:13
  • 获赠金笔:0支
  • 赠出金笔:0支
  • 荣誉徽章:
相关博文
推荐博文
谁看过这篇博文
加载中…
正文 字体大小:

ORACLE的直方图的一些试验 (转载)

(2011-08-16 10:37:34)
标签:

it

分类: 工作区(Oracle/PLSQL/ETL)
ORACLE的直方图的一些试验


直方图有两种类别,等频直方图与等高直方图。
默认的,如果一个倾斜列上的唯一值超过了254个,那么ORACLE会对此列建立等高直方图,否则建立等频直方图。
通过如下方式,建立表TAB,更新字段B,让列B产生倾斜。并在B列上创建索引。
SQL> spool d:\hist.txt
SQL> create table tab (a number, b number);

表已创建。

SQL>
SQL> begin
          for i in 1..10000 loop
            insert into tab values (i, i);
          end loop;
          commit;
        end;
        /

PL/SQL 过程已成功完成。

SQL> update tab set b=5 where b between 6 and 9995;

已更新9990行。
SQL> commit;

提交完成。

SQL> create index ix_tab_b on tab(b);

索引已创建。


然后分析表,强制使列B不产生直方图。
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(OWNNAME    => 'SCOTT',
                                TABNAME    => 'TAB',
                                CASCADE    => TRUE,
                                METHOD_OPT => 'FOR  COLUMNS B SIZE 1 ');
END;
查看视图USER_TAB_HISTOGRAMS,列B上只有最大值,最小值两条记录分别对应端点号(endpoint_number)0和1,这种显示说明列B没有直方图信息。
SQL>SELECT table_name,column_name,endpoint_number,endpoint_value FROM USER_TAB_HISTOGRAMS WHERE TABLE_NAME='TAB' ;

TABLE_NAME                     COLUMN_NAME                              ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ ---------------------------------------- --------------- --------------
TAB                                                                                             1
TAB                                                                                         10000

在没有直方图的情况下,在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 |       (0)| 00:00:01 |
 1 |  TABLE ACCESS BY INDEX ROWID| TAB       1000 |  6000 |       (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IX_TAB_B |  1000 |            (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 |       (0)| 00:00:01 |
 1 |  TABLE ACCESS BY INDEX ROWID| TAB       1000 |  6000 |       (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IX_TAB_B |  1000 |            (0)| 00:00:01 |
----------------------------------------------------------------------------------------


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

在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 |       (0)| 00:00:01 |
 1 |  TABLE ACCESS BY INDEX ROWID| TAB          1 |     6 |       (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IX_TAB_B |     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 |       (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TAB   9991 | 59946 |       (0)| 00:00:01 |
--------------------------------------------------------------------------



查看此时的直方图信息:
SQL>SELECT TABLE_NAME, COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE FROM USER_TAB_HISTOGRAMS
WHERE TABLE_NAME = 'TAB';

TABLE_NAME                     COLUMN_NAME                              ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ ---------------------------------------- --------------- --------------
TAB                                                                                             1
TAB                                                                                             2
TAB                                                                                             3
TAB                                                                                             4
TAB                                                                             9995              5
TAB                                                                             9996           9996
TAB                                                                             9997           9997
TAB                                                                             9998           9998
TAB                                                                             9999           9999
TAB                                                                            10000          10000


其中EDNPOINT_NUMBER是累计值。EDNPOINT_VALUE是列的值。可以看出这种等频直方图统计的列的信息是非常精确的。它为每一个列值分配了一个桶。从执行计划的ROWS部分也可以看出ORACLE计算出来的cardinality是9991,和实际的情况完全吻合。
如果想知道每一个列值对应的数量是多少,需要做一下简单的减法运算:

假如想知道列值等于5的个数,那么可以通过:
9995-4=9991得到。这就是ENDPOINT_NUMBER累计值的含义。

在看看等高直方图的情况。
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(OWNNAME    => 'SCOTT',
                                TABNAME    => 'TAB',
                                CASCADE    => TRUE,
                                METHOD_OPT => 'FOR  COLUMNS B SIZE 8 ');
END;

由于列B有10个唯一值,通过上面的size 8可以强制ORACLE使用等高直方图。
查看直方图信息.
SQL>SELECT TABLE_NAME, COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE FROM USER_TAB_HISTOGRAMS
WHERE TABLE_NAME = 'TAB';

TABLE_NAME                     COLUMN_NAME                              ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ ---------------------------------------- --------------- --------------
TAB                                                                                             1
TAB                                                                                             5
TAB                                                                                         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=5;
已选择9991行。

执行计划
----------------------------------------------------------
Plan hash value: 1995730731
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
 0 | SELECT STATEMENT        9982 | 59892 |       (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TAB   9982 | 59892 |       (0)| 00:00:01 |
--------------------------------------------------------------------------


有没有发现什么?

执行计划的ROWS部分,ORACLE计算出来的cardinality不是特别精确的。9991才是精确值。而等频直方图可以精确到9991,因此可以说等频直方图比等高直方图稳定,精确。
可是现实很多时候,列的唯一值是超过254的。只能使用等高直方图了。


0

阅读 评论 收藏 转载 喜欢 打印举报/Report
  • 评论加载中,请稍候...
发评论

    发评论

    以上网友发言只代表其个人观点,不代表新浪网的观点或立场。

      

    新浪BLOG意见反馈留言板 电话:4000520066 提示音后按1键(按当地市话标准计费) 欢迎批评指正

    新浪简介 | About Sina | 广告服务 | 联系我们 | 招聘信息 | 网站律师 | SINA English | 会员注册 | 产品答疑

    新浪公司 版权所有