anlyze table

最新推荐文章于 2024-04-28 13:48:25 发布

Flexes

最新推荐文章于 2024-04-28 13:48:25 发布

阅读量1k

点赞数

分类专栏： SQL 文章标签： Oracle SQL

SQL 专栏收录该内容

41 篇文章 0 订阅

订阅专栏

它主要是用来对表进行重新统计,当一个表被更新之后,下面3个视图一般不会立马被更新,anlyze table对USER_TABLES,ALL_TABLES,

DBA_TABLES的相关列进行了更新.
 NUM_ROWS、BLOCKS、EMPTY_BLOCKS、AVG_SPACE、CHAIN_COUNT、AVG_ROW_LEN

使用限制

数据字典、外部表、临时表、含有LONG,LOB等字段的表

SQL> conn scott/tiger
Connected.
SQL> drop table t;
Table dropped.
SQL> create table T as select * from dba_objects;
Table created.
SQL> select count(*) from T;
COUNT(*)
----------
45004
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_ANAL
------------------------------ ---------- ---------- ----------- ---------
EMPTY_BLOCKS AVG_SPACE
------------ ----------
T
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_ANAL EMPTY_BLOCKS AVG_SPACE
T 638 44639 97 05-SEP-13 2 915
SQL>