analyze怎么优化oracle,Analyze table对Oracle性能的提升

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

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

比analyze table compute statistics for table for all indexes for all indexed columns 分析了更多的信息

[@more@]

但这里并不是鼓励使用 analyze table 的方法进行分析。

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_na