# 观察analyze table compute statistics 都对什么对象统计了信息

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

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

SQL> create table t as select * from all_objects;
Table created.

SQL> create index t_idx on t(object_id);

Index created.

SQL> analyze table t compute statistics for table
2 for all indexes for all indexed columns;

Table analyzed.

SQL> select t.num_rows, i.num_rows, c.cnt
2 from (select num_rows from user_tables where table_name = 'T') t,
3 (select num_rows from user_indexes where table_name = 'T' ) i,
4 (select count(distinct column_name) cnt from user_tab_histograms where tab
le_name = 'T' ) c
5 /

NUM_ROWS NUM_ROWS CNT
---------- ---------- ----------
31213 31213 1 《------在这里 因为只有1列建立有索引，因此user_tab_histograms 只统计了object_id列的数据分布，这里object_id因为唯一，所以是均匀分布的

SQL> analyze table t delete statistics;

Table analyzed.

SQL> select t.num_rows, i.num_rows, c.cnt
2 from (select num_rows from user_tables where table_name = 'T') t,
3 (select num_rows from user_indexes where table_name = 'T' ) i,
4 (select count(distinct column_name) cnt from user_tab_histograms where ta
ble_name = 'T' ) c;

NUM_ROWS NUM_ROWS CNT
---------- ---------- ----------
0

SQL> analyze table t compute statistics;

Table analyzed.

SQL> select t.num_rows, i.num_rows, c.cnt
2 from (select num_rows from user_tables where table_name = 'T') t,
3 (select num_rows from user_indexes where table_name = 'T' ) i,
4 (select count(distinct column_name) cnt from user_tab_histograms where ta
ble_name = 'T' ) c;

NUM_ROWS NUM_ROWS CNT
---------- ---------- ----------
31213 31213 13 〈------统计了所有的列，但这些列并不是都用在 where col='X' 上的，因此很多信息其实都没有实际意义。

DBMS_STATS 和TABLE的MONITOR结合，可以当表数据量发生10%的变化的时候，自动重新收集统计信息。

• 博文量
139
• 访问量
174613