关于analyze table命令

analyze table t compute statistics = analyze table t compute statistics for table for all indexes for all columns

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

当analyze table t delete statistics 会删除所有的statistics

Oracle collects the following statistics for a table. Statistics marked with an asterisk are always computed exactly. Table statistics, including the status of domain indexes, appear in the data dictionary views USER_TABLES, ALL_TABLES, and DBA_TABLES in the columns shown in parentheses.

• Number of rows (NUM_ROWS)
• * Number of data blocks below the high water mark (that is, the number of data blocks that have been formatted to receive data, regardless whether they currently contain data or are empty) (BLOCKS)
• * Number of data blocks allocated to the table that have never been used (EMPTY_BLOCKS)
• Average available free space in each data block in bytes (AVG_SPACE)
• Number of chained rows (CHAIN_COUNT)
• Average row length, including the row's overhead, in bytes (AVG_ROW_LEN)

Oracle collects the following statistics for an index. Statistics marked with an asterisk are always computed exactly. For conventional indexes, the statistics appear in the data dictionary views USER_INDEXES, ALL_INDEXES, and DBA_INDEXES in the columns in parentheses.

• *Depth of the index from its root block to its leaf blocks (BLEVEL)
• Number of leaf blocks (LEAF_BLOCKS)
• Number of distinct index values (DISTINCT_KEYS)
• Average number of leaf blocks for each index value (AVG_LEAF_BLOCKS_PER_KEY)
• Average number of data blocks for each index value (for an index on a table) (AVG_DATA_BLOCKS_PER_KEY)
• Clustering factor (how well ordered the rows are about the indexed values) (CLUSTERING_FACTOR)

Restrictions on Analyzing Tables

• You cannot use ANALYZE to collect statistics on data dictionary tables.
• You cannot use ANALYZE to collect statistics on an external table. However, you can use the DBMS_STATS package for this purpose.
• You cannot use ANALYZE to collect default statistics on a temporary table. However, if you have created an association between one or more columns of a temporary table and a user-defined statistics type, then you can use ANALYZE to collect the user-defined statistics on the temporary table. (The association must already exist.)
• You cannot compute or estimate statistics for the following column types: REFs, varrays, nested tables, LOBs (LOBs are not analyzed, they are skipped), LONGs, or object types. However, if a statistics type is associated with such a column, then user-defined statistics are collected.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/1698901/viewspace-103273/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/1698901/viewspace-103273/