Oracle analyze table的使用总结

analyze table 一般可以指定分析: 表,所有字段,所有索引字段,所有索引。 若不指定则全部都分析。

语法如下:
analyze table my_table  compute  statistics;  

analyze table my_table compute statistics for table for all indexes for all columns;   

analyze table my_table compute statistics for table for all indexes for all indexed columns

其中:

analyze table my_table compute statistics;  

等价于:

analyze table my_table compute statistics for table for all indexes for all columns;   

 

sample:

analyze table t1 compute statistics for table;
analyze table t2 compute statistics for all columns;
analyze table t3 compute statistics for all indexed columns;

analyze table t5 compute statistics for all indexes; 

analyze table t4 compute statistics;     (不指定)

另外,可以删除分析数据:

analyze table my_table delete statistics;

analyze table my_table delete statistics for table for all indexes for all indexed columns

实例:
首先创建四个临时表t1,t2,t3,t4,和他们相对应的索引:
SQL> create table t1 as select * from user_objects;

Table created.

SQL> create table t2 as select * from user_objects;

Table created.

SQL> create table t3 as select * from user_objects;

Table created.

SQL> create table t4 as select * from user_objects;

Table created.

SQL> create unique index pk_t1_idx on t1(object_id);

Index created.

SQL> create unique index pk_t2_idx on t2(object_id);

Index created.

查看这个时候各个表对应的数据库统计信息(表,字段,索引)
--查看表的统计信息
select table_name,num_rows,blocks,empty_blocks from user_tables where table_name in ('T1','T2','T3','T4');

TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
T1
T2
T3
T4

--查看列的统计信息
set lines 500 pages 999
col table_name for a5
col LOW_VALUE for a15
col HIGH_VALUE for a15
col DENSITY for 99999999999
select table_name,column_name,num_distinct,low_value,high_value,density from user_tab_columns where table_name in ('T1','T2','T3','T4');

TABLE COLUMN_NAME                    NUM_DISTINCT LOW_VALUE       HIGH_VALUE         DENSITY
----- ------------------------------ ------------ --------------- --------------- ----------
T1    OBJECT_NAME
T1    SUBOBJECT_NAME
T1    OBJECT_ID
T1    DATA_OBJECT_ID
T1    OBJECT_TYPE
T1    CREATED
T1    LAST_DDL_TIME
T1    TIMESTAMP
T1    STATUS
T1    TEMPORARY
T1    GENERATED
T1    SECONDARY
T1    NAMESPACE
T1    EDITION_NAME
T2    OBJECT_NAME
T2    SUBOBJECT_NAME
T2    OBJECT_ID
T2    DATA_OBJECT_ID
T2    OBJECT_TYPE
T2    CREATED
T2    LAST_DDL_TIME
T2    TIMESTAMP
T2    STATUS
T2    TEMPORARY
T2    GENERATED
T2    SECONDARY
T2    NAMESPACE
T2    EDITION_NAME
T3    OBJECT_NAME
T3    SUBOBJECT_NAME
T3    OBJECT_ID
T3    DATA_OBJECT_ID
T3    OBJECT_TYPE
T3    CREATED
T3    LAST_DDL_TIME
T3    TIMESTAMP
T3    STATUS
T3    TEMPORARY
T3    GENERATED
T3    SECONDARY
T3    NAMESPACE
T3    EDITION_NAME
T4    OBJECT_NAME
T4    SUBOBJECT_NAME
T4    OBJECT_ID
T4    DATA_OBJECT_ID
T4    OBJECT_TYPE
T4    CREATED
T4    LAST_DDL_TIME
T4    TIMESTAMP
T4    STATUS
T4    TEMPORARY
T4    GENERATED
T4    SECONDARY
T4    NAMESPACE
T4    EDITION_NAME

56 rows selected.

SQL>


--查看索引的统计信息
select table_name,index_name,blevel,leaf_blocks,distinct_keys,  avg_leaf_blocks_per_key avg_leaf_blocks,avg_data_blocks_per_key avg_data_blocks,clustering_factor,num_rows from user_indexes where table_name in ('T1','T2','T3','T4');

TABLE INDEX_NAME                         BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS AVG_DATA_BLOCKS CLUSTERING_FACTOR   NUM_ROWS
----- ------------------------------ ---------- ----------- ------------- --------------- --------------- -----------------
T4    PK_T4_IDX                               1          79         37804               1               1               549      37804
T3    PK_T3_IDX                               1          79         37803               1               1               549      37803
T2    PK_T2_IDX                               1          79         37802               1               1               549      37802
T1    PK_T1_IDX                               1          79         37801               1               1               549      37801

SQL>

现在我们分别对这4个表做不同形式的analyze table处理
analyze table t1 compute statistics for table;
analyze table t2 compute statistics for all columns;
analyze table t3 compute statistics for all indexed columns;
analyze table t4 compute statistics;


SQL> analyze table t1 compute statistics for table;

Table analyzed.

SQL> analyze table t2 compute statistics for all columns;

Table analyzed.

SQL> analyze table t3 compute statistics for all indexed columns;

Table analyzed.

SQL> analyze table t4 compute statistics;

Table analyzed.

SQL>

再回头看看这是的oracle数据库对于各种统计信息
--这是对于表的统计信息
select table_name,num_rows,blocks,empty_blocks from user_tables where table_name in ('T1','T2','T3','T4');

TABLE   NUM_ROWS     BLOCKS EMPTY_BLOCKS
----- ---------- ---------- ------------
T1         37801        514          125
T2
T3
T4         37804        514          125

据此,我们可以据此得出结论,只有我们在analyze table命令中指定了for table或者不指定任何参数的时候,oracle数据库才会给我们统计基于表的统计信息

--这是对于表中字段的统计信息

SQL> select table_name,column_name,num_distinct,low_value,high_value,density from user_tab_columns where table_name in ('T1','T2','T3','T4');

TABLE COLUMN_NAME                    NUM_DISTINCT LOW_VALUE       HIGH_VALUE           DENSITY
----- ------------------------------ ------------ --------------- --------------- ------------
T1    OBJECT_NAME
T1    SUBOBJECT_NAME
T1    OBJECT_ID
T1    DATA_OBJECT_ID
T1    OBJECT_TYPE
T1    CREATED
T1    LAST_DDL_TIME
T1    TIMESTAMP
T1    STATUS
T1    TEMPORARY
T1    GENERATED
T1    SECONDARY
T1    NAMESPACE
T1    EDITION_NAME
T2    OBJECT_NAME                           36920 2F3130303033323 73756E2F7574696            0
                                                  3645F44656C6567 C2F7265736F7572
                                                  617465496E766F6 6365732F4C6F636
                                                  36174696F6E4861 16C654E616D6573

T2    SUBOBJECT_NAME                           84 5030            575248245F57414            0
                                                                  95453545F333238
                                                                  323839373733325
                                                                  F30

T2    OBJECT_ID                             37802 C103            C309581E                   0
T2    DATA_OBJECT_ID                         2447 C103            C309581E                   0
T2    OBJECT_TYPE                              41 434C5553544552  57494E444F57               0
T2    CREATED                                 516 787108180C2624  787505070A1D0B             0
T2    LAST_DDL_TIME                           604 787108180C2624  787505070A1D0B             0
T2    TIMESTAMP                               571 313939372D30342 323031372D30352            0
                                                  D31323A31323A35 D30373A30393A32
                                                  393A3030        383A3130

T2    STATUS                                    2 494E56414C4944  56414C4944                 0
T2    TEMPORARY                                 2 4E              59                         0
T2    GENERATED                                 2 4E              59                         0
T2    SECONDARY                                 1 4E              4E                         0
T2    NAMESPACE                                18 C102            C141                       0
T2    EDITION_NAME                              0                                            0
T3    OBJECT_NAME
T3    SUBOBJECT_NAME
T3    OBJECT_ID                             37803 C103            C309581F                   0
T3    DATA_OBJECT_ID
T3    OBJECT_TYPE
T3    CREATED
T3    LAST_DDL_TIME
T3    TIMESTAMP
T3    STATUS
T3    TEMPORARY
T3    GENERATED
T3    SECONDARY
T3    NAMESPACE
T3    EDITION_NAME
T4    OBJECT_NAME                           36922 2F3130303033323 73756E2F7574696            0
                                                  3645F44656C6567 C2F7265736F7572
                                                  617465496E766F6 6365732F4C6F636
                                                  36174696F6E4861 16C654E616D6573

T4    SUBOBJECT_NAME                           84 5030            575248245F57414            0
                                                                  95453545F333238
                                                                  323839373733325
                                                                  F30

T4    OBJECT_ID                             37804 C103            C3095820                   0
T4    DATA_OBJECT_ID                         2449 C103            C3095820                   0
T4    OBJECT_TYPE                              41 434C5553544552  57494E444F57               0
T4    CREATED                                 518 787108180C2624  787505070A1D17             0
T4    LAST_DDL_TIME                           606 787108180C2624  787505070A1D17             0
T4    TIMESTAMP                               573 313939372D30342 323031372D30352            0
                                                  D31323A31323A35 D30373A30393A32
                                                  393A3030        383A3232

T4    STATUS                                    2 494E56414C4944  56414C4944                 1
T4    TEMPORARY                                 2 4E              59                         1
T4    GENERATED                                 2 4E              59                         1
T4    SECONDARY                                 1 4E              4E                         1
T4    NAMESPACE                                18 C102            C141                       0
T4    EDITION_NAME                              0                                            0

56 rows selected.

据此,在指定for all columns 和不指定任何参数的时候oracle会给所有字段做统计信息,在指定for indexed columns时,oracle只给有索引的字段进行字段信息统,如果我们别有必要给所有字段统计信息时,这个属性就很有用了.

--这里是对于索引的统计信息:
SQL> select table_name,index_name,blevel,leaf_blocks,distinct_keys,  avg_leaf_blocks_per_key avg_leaf_blocks,avg_data_blocks_per_key avg_data_blocks,clustering_factor,num_rows from user_indexes where table_name in ('T1','T2','T3','T4');

TABLE INDEX_NAME                         BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS AVG_DATA_BLOCKS CLUSTERING_FACTOR   NUM_ROWS
----- ------------------------------ ---------- ----------- ------------- --------------- --------------- ----------------- ----------
T4    PK_T4_IDX                               1          79         37804               1               1               549      37804
T3    PK_T3_IDX
T2    PK_T2_IDX
T1    PK_T1_IDX

--从这里我们可以看出,只有表t4有索引统计信息.
--再综合前面的我们就会发现,如果在运行analyze table时我们不指定参数,oracle将收集对于特定表的所有统计信息(表,索引,表字段的统计信息)

====================补充================================
补充,truncate命令不修改以上统计信息
truncate table t1;
truncate table t2;
truncate table t3;
truncate table t4;

SQL> select table_name,num_rows,blocks,empty_blocks from user_tables where table_name in ('T1','T2','T3','T4');

TABLE   NUM_ROWS     BLOCKS EMPTY_BLOCKS
----- ---------- ---------- ------------
T1         37801        514          125
T2
T3
T4         37804        514          125

SQL> select table_name,column_name,num_distinct,low_value,high_value,density from user_tab_columns where table_name in ('T1','T2','T3','T4');

TABLE COLUMN_NAME                    NUM_DISTINCT LOW_VALUE       HIGH_VALUE           DENSITY
----- ------------------------------ ------------ --------------- --------------- ------------
T1    OBJECT_NAME
T1    SUBOBJECT_NAME
T1    OBJECT_ID
T1    DATA_OBJECT_ID
T1    OBJECT_TYPE
T1    CREATED
T1    LAST_DDL_TIME
T1    TIMESTAMP
T1    STATUS
T1    TEMPORARY
T1    GENERATED
T1    SECONDARY
T1    NAMESPACE
T1    EDITION_NAME
T2    OBJECT_NAME                           36920 2F3130303033323 73756E2F7574696            0
                                                  3645F44656C6567 C2F7265736F7572
                                                  617465496E766F6 6365732F4C6F636
                                                  36174696F6E4861 16C654E616D6573

T2    SUBOBJECT_NAME                           84 5030            575248245F57414            0
                                                                  95453545F333238
                                                                  323839373733325
                                                                  F30

T2    OBJECT_ID                             37802 C103            C309581E                   0
T2    DATA_OBJECT_ID                         2447 C103            C309581E                   0
T2    OBJECT_TYPE                              41 434C5553544552  57494E444F57               0
T2    CREATED                                 516 787108180C2624  787505070A1D0B             0
T2    LAST_DDL_TIME                           604 787108180C2624  787505070A1D0B             0
T2    TIMESTAMP                               571 313939372D30342 323031372D30352            0
                                                  D31323A31323A35 D30373A30393A32
                                                  393A3030        383A3130

T2    STATUS                                    2 494E56414C4944  56414C4944                 0
T2    TEMPORARY                                 2 4E              59                         0
T2    GENERATED                                 2 4E              59                         0
T2    SECONDARY                                 1 4E              4E                         0
T2    NAMESPACE                                18 C102            C141                       0
T2    EDITION_NAME                              0                                            0
T3    OBJECT_NAME
T3    SUBOBJECT_NAME
T3    OBJECT_ID                             37803 C103            C309581F                   0
T3    DATA_OBJECT_ID
T3    OBJECT_TYPE
T3    CREATED
T3    LAST_DDL_TIME
T3    TIMESTAMP
T3    STATUS
T3    TEMPORARY
T3    GENERATED
T3    SECONDARY
T3    NAMESPACE
T3    EDITION_NAME
T4    OBJECT_NAME                           36922 2F3130303033323 73756E2F7574696            0
                                                  3645F44656C6567 C2F7265736F7572
                                                  617465496E766F6 6365732F4C6F636
                                                  36174696F6E4861 16C654E616D6573

T4    SUBOBJECT_NAME                           84 5030            575248245F57414            0
                                                                  95453545F333238
                                                                  323839373733325
                                                                  F30

T4    OBJECT_ID                             37804 C103            C3095820                   0
T4    DATA_OBJECT_ID                         2449 C103            C3095820                   0
T4    OBJECT_TYPE                              41 434C5553544552  57494E444F57               0
T4    CREATED                                 518 787108180C2624  787505070A1D17             0
T4    LAST_DDL_TIME                           606 787108180C2624  787505070A1D17             0
T4    TIMESTAMP                               573 313939372D30342 323031372D30352            0
                                                  D31323A31323A35 D30373A30393A32
                                                  393A3030        383A3232

T4    STATUS                                    2 494E56414C4944  56414C4944                 1
T4    TEMPORARY                                 2 4E              59                         1
T4    GENERATED                                 2 4E              59                         1
T4    SECONDARY                                 1 4E              4E                         1
T4    NAMESPACE                                18 C102            C141                       0
T4    EDITION_NAME                              0                                            0

56 rows selected.

SQL> select table_name,index_name,blevel,leaf_blocks,distinct_keys,  avg_leaf_blocks_per_key avg_leaf_blocks,avg_data_blocks_per_key avg_data_blocks,clustering_factor,num_rows from user_indexes where table_name in ('T1','T2','T3','T4');

TABLE INDEX_NAME                         BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS AVG_DATA_BLOCKS CLUSTERING_FACTOR   NUM_ROWS
----- ------------------------------ ---------- ----------- ------------- --------------- --------------- ----------------- ----------
T4    PK_T4_IDX                               1          79         37804               1               1               549      37804
T3    PK_T3_IDX
T2    PK_T2_IDX
T1    PK_T1_IDX

结论:统计信息未改变;

--我们再对以上各表做一次分析
analyze table t1 compute statistics for table;
analyze table t2 compute statistics for all columns;
analyze table t3 compute statistics for all indexed columns;
analyze table t4 compute statistics;

再次查询:
SQL> select table_name,num_rows,blocks,empty_blocks from user_tables where table_name in ('T1','T2','T3','T4');

TABLE   NUM_ROWS     BLOCKS EMPTY_BLOCKS
----- ---------- ---------- ------------
T1             0          0            7
T2
T3
T4         37804        514          125

SQL> select table_name,column_name,num_distinct,low_value,high_value,density from user_tab_columns where table_name in ('T1','T2','T3','T4');

TABLE COLUMN_NAME                    NUM_DISTINCT LOW_VALUE       HIGH_VALUE           DENSITY
----- ------------------------------ ------------ --------------- --------------- ------------
T1    OBJECT_NAME
T1    SUBOBJECT_NAME
T1    OBJECT_ID
T1    DATA_OBJECT_ID
T1    OBJECT_TYPE
T1    CREATED
T1    LAST_DDL_TIME
T1    TIMESTAMP
T1    STATUS
T1    TEMPORARY
T1    GENERATED
T1    SECONDARY
T1    NAMESPACE
T1    EDITION_NAME
T2    OBJECT_NAME                               0                                            0
T2    SUBOBJECT_NAME                            0                                            0
T2    OBJECT_ID                                 0                                            0
T2    DATA_OBJECT_ID                            0                                            0
T2    OBJECT_TYPE                               0                                            0
T2    CREATED                                   0                                            0
T2    LAST_DDL_TIME                             0                                            0
T2    TIMESTAMP                                 0                                            0
T2    STATUS                                    0                                            0
T2    TEMPORARY                                 0                                            0
T2    GENERATED                                 0                                            0
T2    SECONDARY                                 0                                            0
T2    NAMESPACE                                 0                                            0
T2    EDITION_NAME                              0                                            0
T3    OBJECT_NAME
T3    SUBOBJECT_NAME
T3    OBJECT_ID                                 0                                            0
T3    DATA_OBJECT_ID
T3    OBJECT_TYPE
T3    CREATED
T3    LAST_DDL_TIME
T3    TIMESTAMP
T3    STATUS
T3    TEMPORARY
T3    GENERATED
T3    SECONDARY
T3    NAMESPACE
T3    EDITION_NAME
T4    OBJECT_NAME                           36922 2F3130303033323 73756E2F7574696            0
                                                  3645F44656C6567 C2F7265736F7572
                                                  617465496E766F6 6365732F4C6F636
                                                  36174696F6E4861 16C654E616D6573

T4    SUBOBJECT_NAME                           84 5030            575248245F57414            0
                                                                  95453545F333238
                                                                  323839373733325
                                                                  F30

T4    OBJECT_ID                             37804 C103            C3095820                   0
T4    DATA_OBJECT_ID                         2449 C103            C3095820                   0
T4    OBJECT_TYPE                              41 434C5553544552  57494E444F57               0
T4    CREATED                                 518 787108180C2624  787505070A1D17             0
T4    LAST_DDL_TIME                           606 787108180C2624  787505070A1D17             0
T4    TIMESTAMP                               573 313939372D30342 323031372D30352            0
                                                  D31323A31323A35 D30373A30393A32
                                                  393A3030        383A3232

T4    STATUS                                    2 494E56414C4944  56414C4944                 1
T4    TEMPORARY                                 2 4E              59                         1
T4    GENERATED                                 2 4E              59                         1
T4    SECONDARY                                 1 4E              4E                         1
T4    NAMESPACE                                18 C102            C141                       0
T4    EDITION_NAME                              0                                            0

56 rows selected.

SQL> select table_name,index_name,blevel,leaf_blocks,distinct_keys,  avg_leaf_blocks_per_key avg_leaf_blocks,avg_data_blocks_per_key avg_data_blocks,clustering_factor,num_rows from user_indexes where table_name in ('T1','T2','T3','T4');

TABLE INDEX_NAME                         BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS AVG_DATA_BLOCKS CLUSTERING_FACTOR   NUM_ROWS
----- ------------------------------ ---------- ----------- ------------- --------------- --------------- -----------------
T4    PK_T4_IDX                               1          79         37804               1               1               549      37804
T3    PK_T3_IDX
T2    PK_T2_IDX
T1    PK_T1_IDX

--由此得出结论,truncate命令不会修改数据的统计信息,
--也就是如果我们想让CBO利用合理利用数据的统计信息的时候,需要我们及时的使用analyze命令或者dbms_stats重新统计数据的统计信息


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31397003/viewspace-2138869/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/31397003/viewspace-2138869/

  • 0
    点赞
  • 5
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值