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列和索引都有统计信息了
