作者：江枫 来源：Taobao DBA Team 酷勤网收集 2008-07-22
摘要
我只是alert index rebuild online compute statistics，9i却同时收集了表，列和索引的统计信息，这有点属于自做主张，而10g则正确的按照语法，只计算了索引的统计信息。应该说，10g的处理方式要更加合理一些。
在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的这个差异。
--EOF--