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

在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的这个差异。



无觅相关文章插件,快速提升流量

6条评论

  • At 2008.07.23 00:57, 木匠 said:

    我在10.1.0.4上面做了个实验, 情况和10.2.0.4一样, 创建索引的时候 index stats 也有了.

    这两天在读老刘(Lewis)的Cost-Based Oracle Fundamentals, 对 cost 和 statistics 兴趣特别浓.

    – – P.S. Right now I’m reading Cost-Based Oracle Fundamentals by Jonathan Lewis

    还有, 分享一下你的tbsql吗. 哪里可以下载?

    • At 2008.07.23 22:28, NinGoo said:

      tbsql是我自己写的一个shell脚本,其实就是通过case语句输入不同的参数执行不同的sql或者shell而已。里面有部分代码和我的工作环境相关,所有暂时不公开啦,呵呵

      • At 2008.07.24 00:44, 木匠 said:

        The delete you sensitive part, and email to me la.
        save my time to write a new one.

        • At 2014.09.22 21:44, Megapolis Hack said:

          Megapolis Hack…

          » Oracle 9i和10g在create index和rebuild index的统计信息的区别 江边潮未尽,枫红一季秋 — NinGoo’s blog…

          • At 2014.10.01 22:17, publicize said:

            publicize…

            » Oracle 9i和10g在create index和rebuild index的统计信息的区别 江边潮未尽,枫红一季秋 — NinGoo’s blog…

            • At 2014.10.02 17:47, free fifa 15 hack download said:

              free fifa 15 hack download…

              » Oracle 9i和10g在create index和rebuild index的统计信息的区别 江边潮未尽,枫红一季秋 — NinGoo’s blog…


              (Required)
              (Required, will not be published)