ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【江枫 】Oracle 9i和10g在create index和rebuild index的统计信息的区别

【江枫 】Oracle 9i和10g在create index和rebuild index的统计信息的区别

原创 Linux操作系统 作者:idba 时间:2008-07-22 22:50:33 0 删除 编辑

在9.2.0.6和10.2.0.4做了个小小的试验,演示9i和10g对于create index和rebuild index时统计信息的区别。这里列出试验的过程,由于结果比较明显,就懒得写太多文字做说明了。其中tbsql是一个常用脚本的集成环境,tbsql tabstat用户输出一个表以及列和索引的信息,其实就是关联dba_tables/dba_indexes/dba_tab_columns的一个查询。

先来看9.2.0.6的情况:

SQL> create table t as select * from all_objects;
Table created.

SQL> create index t_id on t(object_id);
Index created.
$tbsql tabstat t
           Table    Number           Empty  Average    Chain  Average
OWNER      Name    of Rows  Blocks  Blocks    Space    Count  Row Len
---------- ------ -------- ------- ------- -------- -------- --------
SYS        T

Column             Column                   Distinct
Name               Details                    Values Density
------------------ ------------------------ -------- -------
OWNER              VARCHAR2(30) NOT NULL
OBJECT_NAME        VARCHAR2(30) NOT NULL
SUBOBJECT_NAME     VARCHAR2(30)
OBJECT_ID          NUMBER(22) NOT NULL
DATA_OBJECT_ID     NUMBER(22)
OBJECT_TYPE        VARCHAR2(18)
CREATED            DATE NOT NULL
LAST_DDL_TIME      DATE NOT NULL
TIMESTAMP          VARCHAR2(19)
STATUS             VARCHAR2(7)
TEMPORARY          VARCHAR2(1)
GENERATED          VARCHAR2(1)
SECONDARY          VARCHAR2(1)

13 rows selected.

                        B                          Average     Average
Index                Tree  Leaf       Distinct Leaf Blocks Data Blocks Cluster
Name      Unique    Level  Blks           Keys     Per Key     Per Key  Factor
--------- --------- ----- ----- -------------- ----------- ----------- -------
T_ID      NONUNIQUE                                                    

Index     Column           Col Column
Name      Name             Pos Details
--------- --------------- ---- ------------------------
T_ID      OBJECT_ID                 1 NUMBER(22) NOT NULL

可以看到到表,列和索引都没有统计信息。

SQL> alter index t_id rebuild online compute statistics;
Index altered.
$tbsql tabstat t

        Table    Number           Empty  Average    Chain  Average
OWNER   Name    of Rows  Blocks  Blocks    Space    Count  Row Len
------- ------ -------- ------- ------- -------- -------- --------
SYS     T        25,420     348       0        0        0      100

Column            Column                    Distinct
Name              Details                     Values Density
----------------- ------------------------ --------- -------
OWNER             VARCHAR2(30) NOT NULL
OBJECT_NAME       VARCHAR2(30) NOT NULL
SUBOBJECT_NAME    VARCHAR2(30)
OBJECT_ID         NUMBER(22) NOT NULL         25,420       0
DATA_OBJECT_ID    NUMBER(22)
OBJECT_TYPE       VARCHAR2(18)
CREATED           DATE NOT NULL
LAST_DDL_TIME     DATE NOT NULL
TIMESTAMP         VARCHAR2(19)
STATUS            VARCHAR2(7)
TEMPORARY         VARCHAR2(1)
GENERATED         VARCHAR2(1)
SECONDARY         VARCHAR2(1)

13 rows selected.

                       B                     Average     Average
Index               Tree  Leaf  Distinct Leaf Blocks Data Blocks Cluster
Name     Unique    Level  Blks      Keys     Per Key     Per Key  Factor
-------- --------- ----- ----- --------- ----------- ----------- -------
T_ID     NONUNIQUE     1    56    25,420           1           1  22,731

Index   Column      Col Column
Name    Name        Pos Details
------- ---------- ---- ------------------------
T_ID    OBJECT_ID     1 NUMBER(22) NOT NULL

注意到表,object_id列和索引都有统计信息了

再来看10.2.0.4的情况

SQL> create table t as select * from all_objects;
Table created.

SQL> create index t_id on t(object_id);
Index created.
$tbsql tabstat t
           Table     Number           Empty  Average  Chain  Average
OWNER      Name     of Rows  Blocks  Blocks    Space  Count  Row Len
---------- ------- -------- ------- ------- -------- ------ --------
SYS        T

Column           Column                   Distinct
Name             Details                    Values Density
---------------- ----------------------- --------- -------
OWNER            VARCHAR2(30) NOT NULL
OBJECT_NAME      VARCHAR2(30) NOT NULL
SUBOBJECT_NAME   VARCHAR2(30)
OBJECT_ID        NUMBER(22) NOT NULL
DATA_OBJECT_ID   NUMBER(22)
OBJECT_TYPE      VARCHAR2(19)
CREATED          DATE NOT NULL
LAST_DDL_TIME    DATE NOT NULL
TIMESTAMP        VARCHAR2(19)
STATUS           VARCHAR2(7)
TEMPORARY        VARCHAR2(1)
GENERATED        VARCHAR2(1)
SECONDARY        VARCHAR2(1)

13 rows selected.

                      B                      Average     Average
Index              Tree   Leaf  Distinct Leaf Blocks Data Blocks  Cluster
Name    Unique    Level   Blks      Keys     Per Key     Per Key   Factor
------- --------- ----- ------ --------- ----------- ----------- --------
T_ID    NONUNIQUE     1     21     9,610           1           1      134

Index     Column        Col Column
Name      Name          Pos Details
--------- ------------ ---- ------------------------
T_ID      OBJECT_ID       1 NUMBER(22) NOT NULL

注意到表和列都没有统计信息,而索引有统计信息

SQL> alter index t_id rebuild online compute statistics;
Index altered.
$tbsql tabstat t
        Table     Number           Empty  Average   Chain  Average
OWNER   Name     of Rows  Blocks  Blocks    Space   Count  Row Len
------- ------- -------- ------- ------- -------- ------- --------
SYS     T

Column            Column                       Distinct
Name              Details                        Values Density
----------------- ------------------------ ------------ -------
OWNER             VARCHAR2(30) NOT NULL
OBJECT_NAME       VARCHAR2(30) NOT NULL
SUBOBJECT_NAME    VARCHAR2(30)
OBJECT_ID         NUMBER(22) NOT NULL
DATA_OBJECT_ID    NUMBER(22)
OBJECT_TYPE       VARCHAR2(19)
CREATED           DATE NOT NULL
LAST_DDL_TIME     DATE NOT NULL
TIMESTAMP         VARCHAR2(19)
STATUS            VARCHAR2(7)
TEMPORARY         VARCHAR2(1)
GENERATED         VARCHAR2(1)
SECONDARY         VARCHAR2(1)

13 rows selected.

                       B                       Average     Average
Index               Tree   Leaf   Distinct Leaf Blocks Data Blocks  Cluster
Name     Unique    Level   Blks       Keys     Per Key     Per Key   Factor
-------- --------- ----- ------ ---------- ----------- ----------- --------
T_ID     NONUNIQUE     1     21      9,610           1           1      134

Index      Column         Col Column
Name       Name           Pos Details
---------- ------------- ---- ------------------------
T_ID       OBJECT_ID        1 NUMBER(22) NOT NULL

注意到表和列还是没有统计信息,索引有统计信息。

应该说,10g的处理方式要更加合理一些。我只是alert index rebuild online compute statistics,9i却同时收集了表,列和索引的统计信息,这有点属于自做主张,而10g则正确的按照语法,只计算了索引的统计信息。有个时候,收集列的统计信息,可能导致执行计划选择错误,尤其是这个列是递增列的时候,一定要注意到9i和10g的这个差异。

http://rdc.taobao.com/blog/dba/html/192_differencts_for_statistics_create_or_rebuild_index_9i_

<!--

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/1384/viewspace-406968/,如需转载,请注明出处,否则将追究法律责任。

下一篇: IE6正在出局?
请登录后发表评论 登录
全部评论

注册时间:2008-03-25

  • 博文量
    210
  • 访问量
    394061