OOW2009 PPT:Managing Statistics for Optimal Query Performance

oow2009第三天,听过这堂《Managing Statistics for Optimal Query Performance》,讲课的是Karen Morton女士,非常巧妙的利用一副扑克牌来讲述统计信息的概念,这个ppt让我印象深刻。今天在网上看到Karen Morton已经将该ppt放出来了,引用在这里供大家分享:

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

Read more of this post

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