【江楓 】Oracle 9i和10g在create index和rebuild index的統計資訊的區別

idba發表於2008-07-22
OracleIndexRebuild

在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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章