对于analyze table使用的一些探究(转)

最新推荐文章于 2021-07-31 19:48:49 发布

cuizhi1880

最新推荐文章于 2021-07-31 19:48:49 发布

阅读量115

点赞数

首先创建四个临时表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重新统计数据的统计信息充,truncate命令不修改以上统计信息 http://www.oracle.com.cn/viewthread.php?tid=24461 复制内容到剪贴板代码: 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重新统计数据的统计信息[@more@]

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

转载于:http://blog.itpub.net/212824/viewspace-1021147/