Oracle analyze table的使用總結
語法如下：
analyze table my_table compute statistics;
analyze table my_table compute statistics for table for all indexes for all columns;
analyze table my_table compute statistics for table for all indexes for all indexed columns;
其中：
analyze table my_table compute statistics;
等價於：
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; (不指定）
另外，可以刪除分析資料：
analyze table my_table delete statistics;
analyze table my_table delete statistics for table for all indexes for all indexed columns;
例項：
首先建立四個臨時表t1,t2,t3,t4,和他們相對應的索引：
SQL> create table t1 as select * from user_objects;
Table created.
SQL> create table t2 as select * from user_objects;
Table created.
SQL> create table t3 as select * from user_objects;
Table created.
SQL> create table t4 as select * from user_objects;
Table created.
SQL> create unique index pk_t1_idx on t1(object_id);
Index created.
SQL> create unique index pk_t2_idx on t2(object_id);
Index created.
檢視這個時候各個表對應的資料庫統計資訊(表,欄位,索引)：
--檢視表的統計資訊：
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
T2
T3
T4
--檢視列的統計資訊
set lines 500 pages 999
col table_name for a5
col LOW_VALUE for a15
col HIGH_VALUE for a15
col DENSITY for 99999999999
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 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
T1 NAMESPACE
T1 EDITION_NAME
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
T2 NAMESPACE
T2 EDITION_NAME
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
T3 NAMESPACE
T3 EDITION_NAME
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
T4 NAMESPACE
T4 EDITION_NAME
56 rows selected.
SQL>
--檢視索引的統計資訊
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 INDEX_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS AVG_DATA_BLOCKS CLUSTERING_FACTOR NUM_ROWS
----- ------------------------------ ---------- ----------- ------------- --------------- --------------- -----------------
T4 PK_T4_IDX 1 79 37804 1 1 549 37804
T3 PK_T3_IDX 1 79 37803 1 1 549 37803
T2 PK_T2_IDX 1 79 37802 1 1 549 37802
T1 PK_T1_IDX 1 79 37801 1 1 549 37801
SQL>
現在我們分別對這4個表做不同形式的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;
SQL> analyze table t1 compute statistics for table;
Table analyzed.
SQL> analyze table t2 compute statistics for all columns;
Table analyzed.
SQL> analyze table t3 compute statistics for all indexed columns;
Table analyzed.
SQL> analyze table t4 compute statistics;
Table analyzed.
SQL>
再回頭看看這是的oracle資料庫對於各種統計資訊：
--這是對於表的統計資訊
select table_name,num_rows,blocks,empty_blocks from user_tables where table_name in ('T1','T2','T3','T4');
TABLE NUM_ROWS BLOCKS EMPTY_BLOCKS
----- ---------- ---------- ------------
T1 37801 514 125
T2
T3
T4 37804 514 125
據此，我們可以據此得出結論,只有我們在analyze table命令中指定了for
table或者不指定任何引數的時候,oracle資料庫才會給我們統計基於表的統計資訊
--這是對於表中欄位的統計資訊
SQL> 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 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
T1 NAMESPACE
T1 EDITION_NAME
T2 OBJECT_NAME 36920 2F3130303033323 73756E2F7574696 0
3645F44656C6567 C2F7265736F7572
617465496E766F6 6365732F4C6F636
36174696F6E4861 16C654E616D6573
T2 SUBOBJECT_NAME 84 5030 575248245F57414 0
95453545F333238
323839373733325
F30
T2 OBJECT_ID 37802 C103 C309581E 0
T2 DATA_OBJECT_ID 2447 C103 C309581E 0
T2 OBJECT_TYPE 41 434C5553544552 57494E444F57 0
T2 CREATED 516 787108180C2624 787505070A1D0B 0
T2 LAST_DDL_TIME 604 787108180C2624 787505070A1D0B 0
T2 TIMESTAMP 571 313939372D30342 323031372D30352 0
D31323A31323A35 D30373A30393A32
393A3030 383A3130
T2 STATUS 2 494E56414C4944 56414C4944 0
T2 TEMPORARY 2 4E 59 0
T2 GENERATED 2 4E 59 0
T2 SECONDARY 1 4E 4E 0
T2 NAMESPACE 18 C102 C141 0
T2 EDITION_NAME 0 0
T3 OBJECT_NAME
T3 SUBOBJECT_NAME
T3 OBJECT_ID 37803 C103 C309581F 0
T3 DATA_OBJECT_ID
T3 OBJECT_TYPE
T3 CREATED
T3 LAST_DDL_TIME
T3 TIMESTAMP
T3 STATUS
T3 TEMPORARY
T3 GENERATED
T3 SECONDARY
T3 NAMESPACE
T3 EDITION_NAME
T4 OBJECT_NAME 36922 2F3130303033323 73756E2F7574696 0
3645F44656C6567 C2F7265736F7572
617465496E766F6 6365732F4C6F636
36174696F6E4861 16C654E616D6573
T4 SUBOBJECT_NAME 84 5030 575248245F57414 0
95453545F333238
323839373733325
F30
T4 OBJECT_ID 37804 C103 C3095820 0
T4 DATA_OBJECT_ID 2449 C103 C3095820 0
T4 OBJECT_TYPE 41 434C5553544552 57494E444F57 0
T4 CREATED 518 787108180C2624 787505070A1D17 0
T4 LAST_DDL_TIME 606 787108180C2624 787505070A1D17 0
T4 TIMESTAMP 573 313939372D30342 323031372D30352 0
D31323A31323A35 D30373A30393A32
393A3030 383A3232
T4 STATUS 2 494E56414C4944 56414C4944 1
T4 TEMPORARY 2 4E 59 1
T4 GENERATED 2 4E 59 1
T4 SECONDARY 1 4E 4E 1
T4 NAMESPACE 18 C102 C141 0
T4 EDITION_NAME 0 0
56 rows selected.
據此，在指定for
all columns 和不指定任何引數的時候oracle會給所有欄位做統計資訊,在指定for indexed
columns時,oracle只給有索引的欄位進行欄位資訊統,如果我們別有必要給所有欄位統計資訊時,這個屬性就很有用了.
--這裡是對於索引的統計資訊：
SQL> 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 INDEX_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS AVG_DATA_BLOCKS CLUSTERING_FACTOR NUM_ROWS
----- ------------------------------ ---------- ----------- ------------- --------------- --------------- ----------------- ----------
T4 PK_T4_IDX 1 79 37804 1 1 549 37804
T3 PK_T3_IDX
T2 PK_T2_IDX
T1 PK_T1_IDX
--從這裡我們可以看出,只有表t4有索引統計資訊.
--再綜合前面的我們就會發現,如果在執行analyze
table時我們不指定引數,oracle將收集對於特定表的所有統計資訊(表,索引,表欄位的統計資訊)
====================補充================================
補充,truncate命令不修改以上統計資訊：
truncate
table t1;
truncate table t2;
truncate table t3;
truncate table
t4;
SQL> select table_name,num_rows,blocks,empty_blocks from user_tables where table_name in ('T1','T2','T3','T4');
TABLE NUM_ROWS BLOCKS EMPTY_BLOCKS
----- ---------- ---------- ------------
T1 37801 514 125
T2
T3
T4 37804 514 125
SQL> 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 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
T1 NAMESPACE
T1 EDITION_NAME
T2 OBJECT_NAME 36920 2F3130303033323 73756E2F7574696 0
3645F44656C6567 C2F7265736F7572
617465496E766F6 6365732F4C6F636
36174696F6E4861 16C654E616D6573
T2 SUBOBJECT_NAME 84 5030 575248245F57414 0
95453545F333238
323839373733325
F30
T2 OBJECT_ID 37802 C103 C309581E 0
T2 DATA_OBJECT_ID 2447 C103 C309581E 0
T2 OBJECT_TYPE 41 434C5553544552 57494E444F57 0
T2 CREATED 516 787108180C2624 787505070A1D0B 0
T2 LAST_DDL_TIME 604 787108180C2624 787505070A1D0B 0
T2 TIMESTAMP 571 313939372D30342 323031372D30352 0
D31323A31323A35 D30373A30393A32
393A3030 383A3130
T2 STATUS 2 494E56414C4944 56414C4944 0
T2 TEMPORARY 2 4E 59 0
T2 GENERATED 2 4E 59 0
T2 SECONDARY 1 4E 4E 0
T2 NAMESPACE 18 C102 C141 0
T2 EDITION_NAME 0 0
T3 OBJECT_NAME
T3 SUBOBJECT_NAME
T3 OBJECT_ID 37803 C103 C309581F 0
T3 DATA_OBJECT_ID
T3 OBJECT_TYPE
T3 CREATED
T3 LAST_DDL_TIME
T3 TIMESTAMP
T3 STATUS
T3 TEMPORARY
T3 GENERATED
T3 SECONDARY
T3 NAMESPACE
T3 EDITION_NAME
T4 OBJECT_NAME 36922 2F3130303033323 73756E2F7574696 0
3645F44656C6567 C2F7265736F7572
617465496E766F6 6365732F4C6F636
36174696F6E4861 16C654E616D6573
T4 SUBOBJECT_NAME 84 5030 575248245F57414 0
95453545F333238
323839373733325
F30
T4 OBJECT_ID 37804 C103 C3095820 0
T4 DATA_OBJECT_ID 2449 C103 C3095820 0
T4 OBJECT_TYPE 41 434C5553544552 57494E444F57 0
T4 CREATED 518 787108180C2624 787505070A1D17 0
T4 LAST_DDL_TIME 606 787108180C2624 787505070A1D17 0
T4 TIMESTAMP 573 313939372D30342 323031372D30352 0
D31323A31323A35 D30373A30393A32
393A3030 383A3232
T4 STATUS 2 494E56414C4944 56414C4944 1
T4 TEMPORARY 2 4E 59 1
T4 GENERATED 2 4E 59 1
T4 SECONDARY 1 4E 4E 1
T4 NAMESPACE 18 C102 C141 0
T4 EDITION_NAME 0 0
56 rows selected.
SQL> 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 INDEX_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS AVG_DATA_BLOCKS CLUSTERING_FACTOR NUM_ROWS
----- ------------------------------ ---------- ----------- ------------- --------------- --------------- ----------------- ----------
T4 PK_T4_IDX 1 79 37804 1 1 549 37804
T3 PK_T3_IDX
T2 PK_T2_IDX
T1 PK_T1_IDX
結論：統計資訊未改變；
--我們再對以上各表做一次分析
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;
再次查詢：
SQL> select table_name,num_rows,blocks,empty_blocks from user_tables where table_name in ('T1','T2','T3','T4');
TABLE NUM_ROWS BLOCKS EMPTY_BLOCKS
----- ---------- ---------- ------------
T1 0 0 7
T2
T3
T4 37804 514 125
SQL> 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 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
T1 NAMESPACE
T1 EDITION_NAME
T2 OBJECT_NAME 0 0
T2 SUBOBJECT_NAME 0 0
T2 OBJECT_ID 0 0
T2 DATA_OBJECT_ID 0 0
T2 OBJECT_TYPE 0 0
T2 CREATED 0 0
T2 LAST_DDL_TIME 0 0
T2 TIMESTAMP 0 0
T2 STATUS 0 0
T2 TEMPORARY 0 0
T2 GENERATED 0 0
T2 SECONDARY 0 0
T2 NAMESPACE 0 0
T2 EDITION_NAME 0 0
T3 OBJECT_NAME
T3 SUBOBJECT_NAME
T3 OBJECT_ID 0 0
T3 DATA_OBJECT_ID
T3 OBJECT_TYPE
T3 CREATED
T3 LAST_DDL_TIME
T3 TIMESTAMP
T3 STATUS
T3 TEMPORARY
T3 GENERATED
T3 SECONDARY
T3 NAMESPACE
T3 EDITION_NAME
T4 OBJECT_NAME 36922 2F3130303033323 73756E2F7574696 0
3645F44656C6567 C2F7265736F7572
617465496E766F6 6365732F4C6F636
36174696F6E4861 16C654E616D6573
T4 SUBOBJECT_NAME 84 5030 575248245F57414 0
95453545F333238
323839373733325
F30
T4 OBJECT_ID 37804 C103 C3095820 0
T4 DATA_OBJECT_ID 2449 C103 C3095820 0
T4 OBJECT_TYPE 41 434C5553544552 57494E444F57 0
T4 CREATED 518 787108180C2624 787505070A1D17 0
T4 LAST_DDL_TIME 606 787108180C2624 787505070A1D17 0
T4 TIMESTAMP 573 313939372D30342 323031372D30352 0
D31323A31323A35 D30373A30393A32
393A3030 383A3232
T4 STATUS 2 494E56414C4944 56414C4944 1
T4 TEMPORARY 2 4E 59 1
T4 GENERATED 2 4E 59 1
T4 SECONDARY 1 4E 4E 1
T4 NAMESPACE 18 C102 C141 0
T4 EDITION_NAME 0 0
56 rows selected.
SQL> 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 INDEX_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS AVG_DATA_BLOCKS CLUSTERING_FACTOR NUM_ROWS
----- ------------------------------ ---------- ----------- ------------- --------------- --------------- -----------------
T4 PK_T4_IDX 1 79 37804 1 1 549 37804
T3 PK_T3_IDX
T2 PK_T2_IDX
T1 PK_T1_IDX
--由此得出結論,truncate命令不會修改資料的統計資訊,
--也就是如果我們想讓CBO利用合理利用資料的統計資訊的時候,需要我們及時的使用analyze命令或者dbms_stats重新統計資料的統計資訊
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31397003/viewspace-2138869/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Analyze table對Oracle效能的提升Oracle
- Table邊框使用總結
- MySQL:Analyze table導致'waiting for table flush'MySqlAI
- 【轉】dbms_stats.gather_table_stats與analyze table 的區別
- Oracle Analyze的用法Oracle
- dbms_stats.gather_table_stats與analyze table 的區別[轉貼]
- MySQL 5.7 ANALYZE TABLE分析索引的統計資訊MySql索引
- Bootstrap-Table 總結boot
- Table 引數總結
- analyze table/index validate structure加鎖的問題IndexStruct
- display:table佈局總結
- Table 引數總結 (ZT)
- Oracle index 使用的一個總結OracleIndex
- Oracle之Hint使用總結Oracle
- java 中 set map table list ~~的總結Java
- Analyze 命令的使用方法
- Oracle分析函式使用總結Oracle函式
- analyze table 的各種語法及對應工作機制
- Oracle DBA Round table workshop 結束Oracle
- oracle 索引使用及索引失效總結Oracle索引
- ORACLE鎖的總結Oracle
- dbms_stats與analyze分析彙總
- ORACLE中RECORD、VARRAY、TABLE的使用詳解Oracle
- Oracle 總結Oracle
- 使用Oracle資料泵問題總結Oracle
- Oracle VM使用者手冊總結Oracle
- html~table、table cell的使用HTML
- Oracle資料庫的資料統計(Analyze)Oracle資料庫
- Oracle dbms_stats包和analyze 的比較Oracle
- ORACLE EXPLAIN PLAN的總結OracleAI
- ORACLE中impdp的總結Oracle
- ORACLE中的EXPDP總結Oracle
- ORACLE常用結構和函式使用方法總結Oracle函式
- ListenalbeFuture的使用總結
- git的使用總結Git
- WebView的使用總結WebView
- cmake的使用總結
- 14_深入解析Oracle table cluster結構Oracle