oracle analyze 全部表,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