ITPub博客

首页 > Linux操作系统 > Linux操作系统 > delete与truncate清空表数据对HWM的影响

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

原创 Linux操作系统 作者:oracle-admin 时间:2011-03-24 13:32:57 0 删除 编辑
在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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: oracle scn介绍
下一篇: oracle 11g自动分析
请登录后发表评论 登录
全部评论

注册时间:2011-03-23

  • 博文量
    22
  • 访问量
    105034