1.對索引結構進行統計
1.1analyze index indexname validate structure
聯機文檔的原文:Specify VALIDATE STRUCTURE to validate the structure of the analyzed object.
The statistics collected by this clause are not used by the Oracle Database optimizer, as are statistics collected by the COMPUTE STATISTICS and ESTIMATE STATISTICS clauses.
For an index, Oracle Database verifies the integrity of each data block in the index and checks for block corruption. This clause does not confirm that each row in the table has an index entry or that each index entry points to a row in the table. You can perform these operations by validating the structure of the table with the CASCADE clause.
這個命令主要是用來分析索引的數據塊是否有壞塊，以及根據分析得到的數據（存放在index_stats）來判斷索引是否需要重新建立。
1.2 validate structure有二中模式：online, offline， 默認是offline模式。
聯機文檔原文:
Specify ONLINE to enable Oracle Database to run the validation while DML operations are ongoing within the object. The database reduces the amount of validation performed to allow for concurrency.
Specify OFFLINE, to maximize the amount of validation performed. This setting prevents INSERT, UPDATE, and DELETE statements from concurrently accessing the object during validation but allows queries. This is the default.
Restriction on ONLINE
You cannot specify ONLINE when analyzing a cluster or index.
Note:
When you validate the structure of an object ONLINE, Oracle Database does not collect any statistics, as it does when you validate the structure of the object OFFLINE.
以offline模式分析時，會對表加一個4級別的鎖（表共享），對run系統可能造成一定的影響。
而online模式則沒有表lock的影響，但當以online模式分析時， 在視圖index_stats沒有統計信息。
2.對索引狀態進行統計
analyze index indexname compute statistics
聯機文檔原文:
COMPUTE STATISTICS instructs Oracle Database to compute exact statistics about the analyzed object and store them in the data dictionary. When you analyze a table, both table and column statistics are collected.
Both computed and estimated statistics are used by the Oracle Database optimizer to choose the execution plan for SQL statements that access analyzed objects. These statistics may also be useful to application developers who write such statements.
Column statistics appear in the data dictionary views USER_TAB_COLUMNS, ALL_TAB_COLUMNS, and DBA_TAB_COLUMNS. Histograms appear in the data dictionary views USER_TAB_HISTOGRAMS, DBA_TAB_HISTOGRAMS, and ALL_TAB_HISTOGRAMS; USER_PART_HISTOGRAMS, DBA_PART_HISTOGRAMS, and ALL_PART_HISTOGRAMS; and USER_SUBPART_HISTOGRAMS, DBA_SUBPART_HISTOGRAMS, and ALL_SUBPART_HISTOGRAMS.
總的來說，compute statistics是用來統計index的分析信息，來為CBO服務的。9i之后推薦使用dbms_stats。
ps:
for table的統計信息存在于視圖：user_tables 、all_tables、dba_tables
for all indexes的統計信息存在于視圖: user_indexes 、all_indexes、dba_indexes
for all columns的統計信息存在于試圖：user_tab_columns、all_tab_columns、dba_tab_columns
3.一些sample
3.1使用validate structure分析一個索引是否需要重建
(1)analyze index index_name validate structure;
(2)select t.del_lf_rows_len /t.lf_blk_len from index_stats t where t.name = &index_name;
(3)如果結果大于20%,index就需要被rebuild了。
4.對于分區表，建議使用DBMS_STATS，而不是使用Analyze語句。
(1)可以并行進行，對多個用戶，多個Table
(2)可以得到整個分區表的數據和單個分區的數據。
(3)可以在不同級別上Compute Statistics：單個分區，子分區，全表，所有分區
(4)可以導出統計信息
(5)可以用戶自動收集統計信息
5.DBMS_STATS的缺點
(1)不能Validate Structure
(2)不能收集CHAINED ROWS, 不能收集CLUSTER TABLE的信息，這兩個仍舊需要使用Analyze語句。
(3)DBMS_STATS 默認不對索引進行Analyze，因為默認Cascade是False，需要手工指定為True
6.對于oracle 9里面的External Table，Analyze不能使用，只能使用DBMS_STATS來收集信息。