首先創建四個臨時表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重新統計數據的統計信息

總結

以上是生活随笔為你收集整理的对于analyze table使用的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。