DELTE和TRUNCATE的区别及释放空间对比

可能大家都知道，delete 和 truncate 的大致区别，一个是 DML，另一个是 DDL，且 delete 不适合大数据量删除，但对于小数据量的删除且有索引存在的情况下可谓信手拈来，且可回滚。而 truncate 属于 DDL 语句，不可回滚。

delete 和 truncate 的区别

truncate 是 ddl 语句，delete 是 dml 语句 truncate 速度快，delete 速度慢 truncate 不可以 rollback，delete 可以 rollback（truncate 有风险） truncate 回收高水位线（high watermark），delete 不回收高水位 delete 需要把每个有数据的块读出来，写上 delete 标记，另外还要把 delete 的内容拷贝一份到 undo，并且把它的动作记录到 redo； truncate 就是把系统表（或者 tablespace bitmap）里面把相关的 extent 的标记为未使用，另外把 dba_objects（obj$)里的 data_object_id 换一下。 相当于，truncate 的操作就是先 drop 掉表，并且重建一张新表

实验来验证 truncate 和 delete 的释放空间对比

首先，在 seven 用户下创建一个数据表，表名为 test，数据从 dba_objects 中来。

SQL> create table test as select object_id,object_name from dba_objects;

Table created.

SQL> select count(*) from test;

  COUNT(*)
----------
     72397

SQL> select count(*) from user_extents where segment_name='TEST';

  COUNT(*)
----------
        18

SQL>

可以看出 TEST 表占据了 18 个 extents。

SQL> delete from test;

72397 rows deleted.

SQL> commit;

Commit complete.
SQL> select count(*) from user_extents where segment_name='TEST';

  COUNT(*)
----------
        18

可以轻易的看出，我删除了全部数据后，发现表 TEST 仍然占据了 18 个 extents，也就是说表中的数据虽然被删除了，但是空间并没有被清空，依然被表 TEST 占据着，没有返还给数据库。

下面新建一个跟 TEST 表一模一样的 TEST1 表来测试看看 truncate 对于清空表后，表所占的空间：

SQL>  create table test1 as select object_id,object_name from dba_objects;

Table created.

SQL> select count(*) from user_extents where segment_name='TEST1';

  COUNT(*)
----------
        18
SQL> truncate table test1;

Table truncated.

SQL> commit;

Commit complete.

SQL> select count(*) from user_extents where segment_name='TEST1';

  COUNT(*)
----------
         1

可以一目了然的看出，truncate 表后，user_extents 的值从 18 变成了 1，说明 truncate 将释放后的空间返还给了数据库，以方便给予其他段对象使用；

以上就是 truncate 和 delete 在空间管理上的区别了，究其原因主要是 truncate 语句会回收高水位线（high watermark）,但 delete 作为 DML 语句却不可以。