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

实验环境：OEL+Oracle11.2.0.1

可能大家都知道，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语句却不可以。

之后下一篇文章会对delete和truncate的区别做更进一步的分析。

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

版权所有，转载请注明作者及原文链接，否则追究法律责任!

QQ:      584307876

作者:    Seven

原文链接:http://blog.csdn.net/sunjiapeng/article/details/9613561

邮箱:    seven_ginna@foxmail.com