oracle之nalyze table

oracle之nalyze table
http://www.cnblogs.com/einyboy/archive/2012/08/09/2630321.html

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重新統計資料的統計資訊