Oracle analyze table

oracle的联机文档描述了analyze的作用：
Use the ANALYZE statement to collect non-optimizer statistics, for example, to:

Collect or delete statistics about an index or index partition, table or table partition, index-organized table, cluster, or scalar object attribute.
Validate the structure of an index or index partition, table or table partition, index-organized table, cluster, or object reference (REF).
Identify migrated and chained rows of a table or cluster.

dbms_stats的作用主要是替代analyze的收集统计信息这一块的功能，且在这一方面做了相当大程度上的增强。

以你的analyze table abc compute statistics;
这条为例，生成的统计信息会存在于user_tables这个视图，查看一下select * from user_tables where table_name=’ABC’;
观察一下NUM_ROWS,BLOCKS,AVG_SPACE,AVG_ROW_LEN几列你就会明白，这就是变化。

收集统计信息的目的是为了使基于CBO的执行计划更加准确。

对于Oracle analyze table的使用总结 . 对于Oracle analyze table的使用总结 .analyze table 一般可以指定分析： 表，所有字段，所有索引字段，所有索引。 若不指定则全部都分析。SQL> analyze table my_table compute statistics;  SQL> analyze table my_table compute statistics for table for all indexes for all columns;   SQL> analyze table my_table compute statistics for table for all indexes for all indexed columns;  其中：SQL> analyze table my_table compute statistics;  等价于：SQL> 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;     (不指定）另外，可以删除分析数据：SQL> analyze table my_table delete statistics;SQL> analyze table my_table delete statistics for table for all indexes for all indexed columns;  http://wfly2004.blog.163.com/blog/static/1176427201042891042233/首先创建四个临时表t1,t2,t3,t4,和他们相对应的索引 复制内容到剪贴板 代码:create table t1 as select * from user_objects;create table t2 as select * from user_objects;create table t3 as select * from user_objects;create table t4 as select * from user_objects;create unique index pk_t1_idx on t1(object_id);create unique index pk_t2_idx on t2(object_id);create unique index pk_t3_idx on t3(object_id);create unique index pk_t4_idx on t4(object_id);查看这个时候各个表对应的数据库统计信息(表,字段,索引) 复制内容到剪贴板 代码:--查看表的统计信息select table_name,num_rows,blocks,empty_blocks from user_table where table_names in ('T1','T2','T3','T4');TABLE_NAME        NUM_ROWS        BLOCKS        EMPTY_BLOCKST1                        T2                        T3                        T4                       --查看字段的统计信息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_NAME        COLUMN_NAME        NUM_DISTINCT        LOW_VALUE        HIGH_VALUE        DENSITYT1        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                                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                                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                                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                               --查看索引的统计信息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_rowsfrom user_indexes where table_name in ('T1','T2','T3','T4');TABLE_NAME        INDEX_NAME        BLEVEL        LEAF_BLOCKS        DISTINCT_KEYS        AVG_LEAF_BLOCKS        AVG_DATA_BLOCKS        CLUSTERING_FACTOR        NUM_ROWST1        PK_T1_IDX                                                        T2        PK_T2_IDX                                                        T3        PK_T3_IDX                                                        T4        PK_T4_IDX          现在我们分别对这个表做不同形式的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;我们再回头看看这是的oracle数据库对于各种统计信息 复制内容到剪贴板 代码:--这是对于表的统计信息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_BLOCKST1        3930        55        1T2                        T3                        T4        3933        55        1--我们可以据此得出结论,只有我们在analyze table命令中指定了for table或者不指定任何参数的时候,oracle数据库才会给我们统计基于表的统计信息--这是对于表中字段的统计信息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_NAME        COLUMN_NAME        NUM_DISTINCT        LOW_VALUE        HIGH_VALUE        DENSITYT1        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                                T2        OBJECT_NAME        3823        41423030        D3F1BBB736D4C2B7DDCFFABBA7C7E5B5A5        .000270447891062615T2        SUBOBJECT_NAME        77        503031        52455354        .012987012987013T2        OBJECT_ID        3930        C304062D        C30F4619        .000254452926208651T2        DATA_OBJECT_ID        3662        C304062D        C30F4619        .000273074822501365T2        OBJECT_TYPE        15        4441544142415345204C494E4B        56494557        .000127194098193844T2        CREATED        3684        7867081E111F33        7868071211152F        .000547559423988464T2        LAST_DDL_TIME        3574        7867081E11251B        7868071211152F        .000565522924083892T2        TIMESTAMP        3649        323030332D30382D33303A31363A33303A3530        323030342D30372D31383A31363A32303A3436        .000559822349362313T2        STATUS        2        494E56414C4944        56414C4944        .000127194098193844T2        TEMPORARY        2        4E        59        .000127194098193844T2        GENERATED        2        4E        59        .000127194098193844T2        SECONDARY        2        4E        59        .000127194098193844T3        OBJECT_NAME                                T3        SUBOBJECT_NAME                                T3        OBJECT_ID        3931        C304062D        C30F461A        .000254388196387688T3        DATA_OBJECT_ID                                T3        OBJECT_TYPE                                T3        CREATED                                T3        LAST_DDL_TIME                                T3        TIMESTAMP                                T3        STATUS                                T3        TEMPORARY                                T3        GENERATED                                T3        SECONDARY                                T4        OBJECT_NAME        3825        41423030        D3F1BBB736D4C2B7DDCFFABBA7C7E5B5A5        .000261437908496732T4        SUBOBJECT_NAME        77        503031        52455354        .012987012987013T4        OBJECT_ID        3932        C304062D        C30F461B        .000254323499491353T4        DATA_OBJECT_ID        3664        C304062D        C30F461B        .00027292576419214T4        OBJECT_TYPE        15        4441544142415345204C494E4B        56494557        .0666666666666667T4        CREATED        3685        7867081E111F33        78680712111530        .000271370420624152T4        LAST_DDL_TIME        3575        7867081E11251B        78680712111530        .00027972027972028T4        TIMESTAMP        3650        323030332D30382D33303A31363A33303A3530        323030342D30372D31383A31363A32303A3437        .000273972602739726T4        STATUS        2        494E56414C4944        56414C4944        .5T4        TEMPORARY        2        4E        59        .5T4        GENERATED        2        4E        59        .5T4        SECONDARY        2        4E        59        .5/*在这个结果中我们可以看到,oracle数据库给t2,t4的所有字段都做了统计信息.对表t3的object_id(索引字段)做了统计信息.由此得出结论,在指定for all columns 和不指定任何参数的时候oracle会给所有字段做统计信息,在指定for indexed columns时,oracle只给[b]有索引的字段进行字段信息统计[/b],如果我们别有必要给所有字段统计信息时,这个属性就很有用了.*/--这里是对于索引的统计信息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_rowsfrom user_indexes where table_name in ('T1','T2','T3','T4');TABLE_NAME        INDEX_NAME        BLEVEL        LEAF_BLOCKS        DISTINCT_KEYS        AVG_LEAF_BLOCKS        AVG_DATA_BLOCKS        CLUSTERING_FACTOR        NUM_ROWST1        PK_T1_IDX                                                        T2        PK_T2_IDX                                                        T3        PK_T3_IDX                                                        T4        PK_T4_IDX        1        9        3932        1        1        2143        3932--从这里我们可以看出,只有表t4有索引统计信息.--再综合前面的我们就会发现,如果在运行analyze table是我们不指定参数,oracle将收集对于特定表的所有统计信息(表,索引,表字段的统计信息)补充,truncate命令不修改以上统计信息复制内容到剪贴板 代码:truncate table t1;truncate table t2;truncate table t3;truncate table t4;--我们在查看表和索引的统计信息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_BLOCKST1        3930        55        1T2                        T3                        T4        3933        55        1--索引的统计信息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_rowsfrom user_indexes where table_name in ('T1','T2','T3','T4');TABLE_NAME        INDEX_NAME        BLEVEL        LEAF_BLOCKS        DISTINCT_KEYS        AVG_LEAF_BLOCKS        AVG_DATA_BLOCKS        CLUSTERING_FACTOR        NUM_ROWST1        PK_T1_IDX                                                        T2        PK_T2_IDX                                                        T3        PK_T3_IDX                                                        T4        PK_T4_IDX        1        9        3932        1        1        2143        3932--我们再对以上各表做一次分析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;--现在再来查看表和索引的统计信息select table_name,num_rows,blocks,empty_blocks,initial_extent,'8192' block_size from user_tables where table_name in ('T1','T2','T3','T4');TABLE_NAME        NUM_ROWS        BLOCKS        EMPTY_BLOCKS        INITIAL_EXTENT        BLOCK_SIZET1        0        0        8        65536        8192T2                                65536        8192T3                                65536        8192T4        0        0        8        65536        8192--索引的统计信息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_rowsfrom user_indexes where table_name in ('T1','T2','T3','T4');TABLE_NAME        INDEX_NAME        BLEVEL        LEAF_BLOCKS        DISTINCT_KEYS        AVG_LEAF_BLOCKS        AVG_DATA_BLOCKS        CLUSTERING_FACTOR        NUM_ROWST1        PK_T1_IDX                                                        T2        PK_T2_IDX                                                        T3        PK_T3_IDX                                                        T4        PK_T4_IDX        0        0        0        0        0        0        0--由此得出结论,truncate命令不会修改数据的统计信息,--也就是如果我们想让CBO利用合理利用数据的统计信息的时候,需要我们及时的使用analyze命令或者dbms_stats重新统计数据的统计信息分类: OralceRac