对于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_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重新统计数据的统计信息




本文转自einyboy博客园博客,原文链接:http://www.cnblogs.com/einyboy/archive/2012/08/09/2630321.html,如需转载请自行联系原作者。

目录
相关文章
|
6月前
|
Oracle 关系型数据库 数据库
Oracle数据恢复—Oracle数据库误truncate table的数据恢复案例
北京某国企客户Oracle 11g R2数据库误truncate table CM_CHECK_ITEM_HIS,表数据丢失,业务查询到该表时报错,数据库的备份不可用,无法查询表数据。 Oracle数据库执行Truncate命令的原理:在执行Truncate命令后ORACLE会在数据字典和Segment Header中更新表的Data Object ID,但不会修改实际数据部分的块。由于数据字典与段头的DATA_OBJECT_ID与后续的数据块中的并不一致,所以ORACLE服务进程在读取全表数据时不会读取到已经被TRUNCATE的记录,但是实际数据未被覆盖。
Oracle数据恢复—Oracle数据库误truncate table的数据恢复案例
|
Oracle 关系型数据库
oracle 12c新功能 recover table恢复单个表
不支持sys用户和system表空间的表
141 0
|
存储 Oracle 关系型数据库
【数据库】解决 oracle: ORA-01653: unable to extend table *.LINEORDER by 1024 in tablespace SYSTEM
【数据库】解决 oracle: ORA-01653: unable to extend table *.LINEORDER by 1024 in tablespace SYSTEM
541 0
【数据库】解决 oracle: ORA-01653: unable to extend table *.LINEORDER by 1024 in tablespace SYSTEM
|
Oracle 关系型数据库 BI
Oracle-分析函数_总结
Oracle-分析函数_总结
|
SQL Oracle 算法
Oracle总结【SQL细节、多表查询、分组查询、分页】下
在之前已经大概了解过Mysql数据库和学过相关的Oracle知识点,但是太久没用过Oracle了,就基本忘了…印象中就只有基本的SQL语句和相关一些概念….写下本博文的原因就是记载着Oracle一些以前没注意到的知识点…以后或许会有用… 实例与数据库概念
298 0
Oracle总结【SQL细节、多表查询、分组查询、分页】下
|
SQL Oracle 关系型数据库
Oracle总结【SQL细节、多表查询、分组查询、分页】上
在之前已经大概了解过Mysql数据库和学过相关的Oracle知识点,但是太久没用过Oracle了,就基本忘了…印象中就只有基本的SQL语句和相关一些概念….写下本博文的原因就是记载着Oracle一些以前没注意到的知识点…以后或许会有用… 实例与数据库概念
147 0
Oracle总结【SQL细节、多表查询、分组查询、分页】上
|
SQL 存储 Oracle
Oracle总结【视图、索引、事务、用户权限、批量操作】三
在Oracle总结的第一篇中,我们已经总结了一些常用的SQL相关的知识点了…那么本篇主要总结关于Oralce视图、序列、事务的一些内容… 在数据库中,我们可以把各种的SQL语句分为四大类… (1)DML(数据操纵语言):select,insert,update,delete (2)DDL(数据定义语言):create table,alter table,drop table,truncate table (3)DCL(数据控制语言):grant select any table to scott/revoke select any table from scott (4)TCL(事务
162 0
Oracle总结【视图、索引、事务、用户权限、批量操作】三
|
SQL Oracle 关系型数据库
Oracle总结【视图、索引、事务、用户权限、批量操作】二
在Oracle总结的第一篇中,我们已经总结了一些常用的SQL相关的知识点了…那么本篇主要总结关于Oralce视图、序列、事务的一些内容… 在数据库中,我们可以把各种的SQL语句分为四大类… (1)DML(数据操纵语言):select,insert,update,delete (2)DDL(数据定义语言):create table,alter table,drop table,truncate table (3)DCL(数据控制语言):grant select any table to scott/revoke select any table from scott (4)TCL(事务
188 0
Oracle总结【视图、索引、事务、用户权限、批量操作】二
|
SQL 存储 Oracle
Oracle总结【视图、索引、事务、用户权限、批量操作】 一
在Oracle总结的第一篇中,我们已经总结了一些常用的SQL相关的知识点了…那么本篇主要总结关于Oralce视图、序列、事务的一些内容… 在数据库中,我们可以把各种的SQL语句分为四大类… (1)DML(数据操纵语言):select,insert,update,delete (2)DDL(数据定义语言):create table,alter table,drop table,truncate table (3)DCL(数据控制语言):grant select any table to scott/revoke select any table from scott (4)TCL(事务
174 0
Oracle总结【视图、索引、事务、用户权限、批量操作】 一