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

在oracle里,使用delete删除数据以后，数据库的存储容量不会减少，而且使用delete删除某个表的数据以后，查询这张表的速度和删除之前一样，不会发生变化。

因为oralce有一个HWM高水位标记（或者说高水位线），它是oracle的一个表使用空间最高水位线。当插入了数据以后，高水位线就会上涨，但是如果你采用delete语句删除数据的话，数据虽然被删除了，但是高水位线却没有降低，还是你刚才删除数据以前那么高的水位。除非使用truncate删除数据。那么，这条高水位线在日常的增删操作中只会上涨，不会下跌，所以数据库容量也只会上升，不会下降。而使用select语句查询数据时，数据库会扫描高水位线以下的数据块，因为高水位线没有变化，所以扫描的时间不会减少，所以才会出现使用 delete删除数据以后，查询的速度还是和delete以前一样。

因此我们可以得出delete 并不会释放表空间，要释放表空间，需要使用truncate语句。
下面我们在测试一下。
1）创建测试表并插入测试数据；
SQL> CREATE TABLE w.w AS SELECT * FROM dba_objects;

Table created.
2）查看表中分配块，区大小；
SQL> SELECT segment_name, segment_type, blocks, extents FROM dba_segments WHERE segment_name = ' W';   ------segment_name的值一定要大些

SEGMENT_NAME                                      SEGMENT_TYPE         BLOCKS    EXTENTS
--------------------------------------------------------------------------------- ------------------ ---------- ----------
W                                          TABLE            4264       49
blocks列为分配数据块儿数，extents列为分配区块儿数
3）分析表w；
SQL> ANALYZE TABLE w ESTIMATE STATISTICS;

Table analyzed.
4）查询表高水位线；
SQL> SELECT blocks, empty_blocks, num_rows FROM USER_TABLES WHERE TABLE_NAME = 'W';

    BLOCKS EMPTY_BLOCKS   NUM_ROWS
---------- ------------ ----------
      4214         50     294935
blocks列为高水位线（占用w表数据块儿数），empty_blocks列为表w空闲块儿数
5）现在将w表delete，在查看高水位线
SQL> DELETE FROM w;

290232 rows deleted.

SQL> commit;

Commit complete.

SQL> ANALYZE TABLE w ESTIMATE STATISTICS;

Table analyzed.

SQL> SELECT blocks, empty_blocks, num_rows FROM USER_TABLES WHERE TABLE_NAME = 'W';

    BLOCKS EMPTY_BLOCKS   NUM_ROWS
---------- ------------ ----------
      4214         50      0                   ------高水位线并未下降，还是4214
发现此表高水位线并未减少，证明delete只是删除表中数据块的记录，但并不会使表中的高水位线下降， 在进行全表扫描时会Oracle会扫描表中高水位线下的所有数据块，
因此数据虽然被删除了，但查询时有可能还是很慢。所以在进行大表删除时应使用truncate语句，看下面实验:
1）创建测试数据；
SQL> INSERT INTO w.w SELECT * FROM dba_objects;

72558 rows created.

SQL> commit;

Commit complete.

SQL> INSERT INTO w.w SELECT * FROM dba_objects;

72558 rows created.

SQL> commit;

Commit complete.
2）分析表，并查看高水位线；
SQL> ANALYZE TABLE w ESTIMATE STATISTICS;

Table analyzed.

SQL> SELECT blocks, empty_blocks, num_rows FROM USER_TABLES WHERE TABLE_NAME = 'W';

    BLOCKS EMPTY_BLOCKS   NUM_ROWS
---------- ------------ ----------
      2134         42     149424
3）使用truncate删除表w的数据；
SQL> TRUNCATE TABLE w;

Table truncated.
4）再次分析表并查看高水位线；
SQL> ANALYZE TABLE w ESTIMATE STATISTICS;

Table analyzed.

SQL> SELECT blocks, empty_blocks, num_rows FROM USER_TABLES WHERE TABLE_NAME = 'W';

    BLOCKS EMPTY_BLOCKS   NUM_ROWS
---------- ------------ ----------
     0          8      0                         ------高水位线着实下降到初始值0了

当然如果我们执行了delete并commit了之后，就算不使用truncate命令，也是可以降低hwm的，我们可以使用move或者shrink（oracle10g开始才支持）来调整（或者说初始化）高水位线。下面我们就来测试下。
1）创建测试数据，并查看高水位线；
SQL> INSERT INTO w.w SELECT * FROM dba_objects;

72558 rows created.

SQL> commit;

Commit complete.

SQL> INSERT INTO w.w SELECT * FROM dba_objects;

72558 rows created.

SQL> commit;

Commit complete.
SQL> SELECT blocks, empty_blocks, num_rows FROM USER_TABLES WHERE TABLE_NAME = 'W';

    BLOCKS EMPTY_BLOCKS   NUM_ROWS
---------- ------------ ----------
      2134         42     149199          -----高水位线2134

2）执行delete删除，并分析表；
SQL> SELECT COUNT(*) FROM w;

  COUNT(*)
----------
    145116
SQL> DELETE FROM w;

145116 rows deleted.

SQL> commit;

Commit complete.
SQL> ANALYZE TABLE w ESTIMATE STATISTICS;

Table analyzed.
3）查看高水位线是否下降；
SQL> SELECT blocks, empty_blocks, num_rows FROM USER_TABLES WHERE TABLE_NAME = 'W';

    BLOCKS EMPTY_BLOCKS   NUM_ROWS
---------- ------------ ----------
      2134         42     149199        --------并未下降
4）使用move或者shrink命令调整高水位线，并分析表；
SQL> ALTER TABLE w ENABLE ROW MOVEMENT;

Table altered.

SQL> ALTER TABLE w SHRINK SPACE;

Table altered.
SQL> ANALYZE TABLE w ESTIMATE STATISTICS;

Table analyzed.
5）查看高水位线变化；
SQL> SELECT blocks, empty_blocks, num_rows FROM USER_TABLES WHERE TABLE_NAME = 'W';

    BLOCKS EMPTY_BLOCKS   NUM_ROWS
---------- ------------ ----------
     1          7      0           -----高水位线变为1了
测试完成。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23502881/viewspace-690384/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23502881/viewspace-690384/