用Delete与Truncate清除表数据对高水位的影响

众所周知，Oracle段都有一个在段内容纳数据块的上限，我们把这个上限称为"High Water Mark"（HWM）。这个HWM是一个标记，用来说明已经有多少没有使用的数据块分配给这个segment。原则上HWM只会增大，不会缩小，即使将表中的数据都删除，HWM还是为原值。HWM就像一个水库的历史最高水位，这也是为何会称之为“高水位”的缘故。实际环境中随着我们表中数据的不断增长，表的高水位也被不断的推高。当高水位达到一定程度之后，会对该表上的SQL查询效率产生负面影响，因此需要采取有效措施降低高水位。下面做个测试，来比较下如何删除数据才能有效降低高水位。注意，我的测试环境为11.2.0.3，其他版本的测试结果可能略有不同。

delete与truncate清空表数据对HWM的影响 

--创建测试环境

SQL> conn / as sysdba

SQL> create tablespace zlm datafile '/u01/app/oracle/oradata/zlm11g/zlm01.dbf' size 50m;

 

Tablespace created.

 

SQL> create table zlm.zlm1 as select * from dba_objects;

 

Table created.

 

SQL> set lin 130 pages 130

SQL> select table_name,num_rows,blocks,empty_blocks,avg_row_len,last_analyzed from dba_tables where table_name like 'ZLM%';

 

TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN LAST_ANALYZED

------------------------------ ---------- ---------- ------------ ----------- ------------------

ZLM1

 

--分析表的统计信息

SQL> analyze table zlm.zlm1 estimate statistics;

 

Table analyzed.

 

SQL> select table_name,num_rows,blocks,empty_blocks,avg_row_len,last_analyzed from dba_tables where table_name like 'ZLM%';

 

TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN LAST_ANALYZED

------------------------------ ---------- ---------- ------------ ----------- ------------------

ZLM1                                77341       1101           51         100 27-SEP-14

 

SQL> select count(*) from zlm.zlm1;

 

  COUNT(*)

----------

     75541

 

可以看到，用estimate分析的表的行数会不准确，差了1800条记录，我们用compute来分析表

 

SQL> analyze table zlm.zlm1 compute statistics;

 

Table analyzed.

 

SQL> select table_name,num_rows,blocks,empty_blocks,avg_row_len,last_analyzed from dba_tables where table_name like 'ZLM%';

 

TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN LAST_ANALYZED

------------------------------ ---------- ---------- ------------ ----------- ------------------

ZLM1                                75541      1101           51        100 27-SEP-14

 

也可以用dbms_stats包来收集表的统计信息

 

SQL> exec dbms_stats.gather_table_stats('ZLM','ZLM1')

 

PL/SQL procedure successfully completed.

 

SQL> select table_name,num_rows,blocks,empty_blocks,avg_row_len,last_analyzed from dba_tables where table_name like 'ZLM%';

 

TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN LAST_ANALYZED

------------------------------ ---------- ---------- ------------ ----------- ------------------