Oracle analyze table的作用
程序员文章站
2022-06-21 20:58:00
oracle的联机文档描述了analyze的作用：
use the analyze statement to collect non-optimizer statistics, for exampl...
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;
https://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_blocks
t1
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 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
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_rows
from 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_rows
t1 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_blocks
t1 3930 55 1
t2
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 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
t2 object_name 3823 41423030 d3f1bbb736d4c2b7ddcffabba7c7e5b5a5 .000270447891062615
t2 subobject_name 77 503031 52455354 .012987012987013
t2 object_id 3930 c304062d c30f4619 .000254452926208651
t2 data_object_id 3662 c304062d c30f4619 .000273074822501365
t2 object_type 15 4441544142415345204c494e4b 56494557 .000127194098193844
t2 created 3684 7867081e111f33 7868071211152f .000547559423988464
t2 last_ddl_time 3574 7867081e11251b 7868071211152f .000565522924083892
t2 timestamp 3649 323030332d30382d33303a31363a33303a3530 323030342d30372d31383a31363a32303a3436 .000559822349362313
t2 status 2 494e56414c4944 56414c4944 .000127194098193844
t2 temporary 2 4e 59 .000127194098193844
t2 generated 2 4e 59 .000127194098193844
t2 secondary 2 4e 59 .000127194098193844
t3 object_name
t3 subobject_name
t3 object_id 3931 c304062d c30f461a .000254388196387688
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 3825 41423030 d3f1bbb736d4c2b7ddcffabba7c7e5b5a5 .000261437908496732
t4 subobject_name 77 503031 52455354 .012987012987013
t4 object_id 3932 c304062d c30f461b .000254323499491353
t4 data_object_id 3664 c304062d c30f461b .00027292576419214
t4 object_type 15 4441544142415345204c494e4b 56494557 .0666666666666667
t4 created 3685 7867081e111f33 78680712111530 .000271370420624152
t4 last_ddl_time 3575 7867081e11251b 78680712111530 .00027972027972028
t4 timestamp 3650 323030332d30382d33303a31363a33303a3530 323030342d30372d31383a31363a32303a3437 .000273972602739726
t4 status 2 494e56414c4944 56414c4944 .5
t4 temporary 2 4e 59 .5
t4 generated 2 4e 59 .5
t4 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_rows
from 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_rows
t1 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_blocks
t1 3930 55 1
t2
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_rows
from 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_rows
t1 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_size
t1 0 0 8 65536 8192
t2 65536 8192
t3 65536 8192
t4 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_rows
from 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_rows
t1 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