Oracle analyze table

官方对Analyze table的解释为
http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_4005.htm#SQLRF01105

它主要是用来对表进行重新统计,当一个表被更新之后,下面3个视图一般不会立马被更新,anlyze table对USER_TABLES,ALL_TABLES, DBA_TABLES的相关列进行了更新.
NUM_ROWS
BLOCKS
EMPTY_BLOCKS
AVG_SPACE
CHAIN_COUNT
AVG_ROW_LEN

使用限制
数据字典
外部表
临时表
含有LONG,LOB等字段的表

例子

SQL> create table T as select * from dba_objects;

Table created.

SQL> select count(*) from T;

  COUNT(*)
----------
     78365

SQL> select table_name,blocks,num_rows,avg_row_len,last_analyzed,empty_blocks,avg_space from user_tables where table_name='T';

TABLE_NAME                         BLOCKS   NUM_ROWS AVG_ROW_LEN LAST_ANAL EMPTY_BLOCKS  AVG_SPACE
------------------------------ ---------- ---------- ----------- --------- ------------ ----------
T

SQL> delete from T where rownum<=365;

365 rows deleted.

SQL> select table_name,blocks,num_rows,avg_row_len,last_analyzed,empty_blocks,avg_space from user_tables where table_name='T';

TABLE_NAME                         BLOCKS   NUM_ROWS AVG_ROW_LEN LAST_ANALYZED       EMPTY_BLOCKS  AVG_SPACE
------------------------------ ---------- ---------- ----------- ------------------- ------------ ----------
T                                    1142      78365         100 2012-10-17 13:22:18           10        863
这里如果没有analyze table则,删除365行记录后,user_tables记录没有变动.

SQL> analyze table T compute statistics;

Table analyzed.

SQL> select table_name,blocks,num_rows,avg_row_len,last_analyzed,empty_blocks,avg_space from user_tables where table_name='T';

TABLE_NAME                         BLOCKS   NUM_ROWS AVG_ROW_LEN LAST_ANALYZED       EMPTY_BLOCKS  AVG_SPACE
------------------------------ ---------- ---------- ----------- ------------------- ------------ ----------
T                                    1142      78000         100 2012-10-17 13:51:07           10        891