[b]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;
得出结论,只有我们在analyze table命令中指定了for table或者不指定任何参数的时候,oracle数据库才会给我们统计基于表的统计信息[/b]
analyze TABLE a COMPUTE statistics FOR TABLE;对表做统计分析,仅仅影响all_tables中的相关数据
analyze TABLE b COMPUTE statistics FOR ALL indexed columns;对索引列做统计分析,仅仅影响all_tab_columns中索引列相关数据
analyze TABLE c COMPUTE statistics FOR TABLE FOR ALL indexed columns;对表和索引列做统计分析,影响all_tables中对应表名的相关数据及all_tab_columns中对应 索引列的相关数据
analyze TABLE d COMPUTE statistics;分析表的所有列和表的整体信息,影响all_tables中对应表名的相关数据及all_tab_columns中属于表d的 所有列的相关数据
来源:http://space.itpub.net/1698901/viewspace-103273
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.
analyze table t2 compute statistics for all columns;
analyze table t3 compute statistics for all indexed columns;
analyze table t4 compute statistics;
得出结论,只有我们在analyze table命令中指定了for table或者不指定任何参数的时候,oracle数据库才会给我们统计基于表的统计信息[/b]
analyze TABLE a COMPUTE statistics FOR TABLE;对表做统计分析,仅仅影响all_tables中的相关数据
analyze TABLE b COMPUTE statistics FOR ALL indexed columns;对索引列做统计分析,仅仅影响all_tab_columns中索引列相关数据
analyze TABLE c COMPUTE statistics FOR TABLE FOR ALL indexed columns;对表和索引列做统计分析,影响all_tables中对应表名的相关数据及all_tab_columns中对应 索引列的相关数据
analyze TABLE d COMPUTE statistics;分析表的所有列和表的整体信息,影响all_tables中对应表名的相关数据及all_tab_columns中属于表d的 所有列的相关数据
来源:http://space.itpub.net/1698901/viewspace-103273
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.