实例对比Oracle中truncate和delete的区别

实例对比Oracle中truncate和delete的区别

删除表中的数据的方法有delete,truncate,

它们都是删除表中的数据,而不能删除表结构,delete 可以删除整个表的数据也可以删除表中某一条或N条满足条件的数据,而truncate只能删除整个表的数据,一般我们把delete 操作收作删除表,而truncate操作叫作截断表.

truncate操作与delete操作对比

操作	回滚	高水线	空间	效率
Truncate	不能	下降	回收	快
delete	可以	不变	不回收	慢

 

下面分别用实例查看它们的不同

1.回滚

首先要明白两点

1.在oracle 中数据删除后还能回滚是因为它把原始数据放到了undo表空间,

2.DML语句使用undo表空间,DDL语句不使用undo,而delete是DML语句,truncate是DDL语句,别外DDL语句是隐式提交.

所以truncate操用不能回滚,而delete操作可以.

两种操作对比(首先新建一个表,并插入数据)

SQL> create table t

  2  (

  3  i number

  4  );

Table created.

SQL> insert into t values(10);

SQL> commit;

Commit complete.

SQL> select * from t;

         I

----------

        10

Delete删除,然后回滚

SQL> delete from t;

1 row deleted.

SQL> select * from t;

no rows selected

#删除后回滚

SQL> rollback;

Rollback complete.

SQL> select * from t;

         I

----------

        10

Truncate截断表,然后回滚.

SQL> truncate table t;

Table truncated.

SQL> rollback;

Rollback complete.

SQL> select * from t;

no rows selected

可见delete删除表还可以回滚,而truncate截断表就不能回滚了.(前提是delete操作没有提交)

2.高水线

所有的Oracle表都有一个容纳数据的上限（很象一个水库历史最高的水位），我们把这个上限称为“high water mark”或HWM。这个HWM是一个标记(专门有一个数据块用来记录高水标记等)，用来说明已经有多少数据块分配给这个表. HWM通常增长的幅度为一次5个数据块.

delete语句不影响表所占用的数据块, 高水线(high watermark)保持原位置不动

truncate 语句缺省情况下空间释放,除非使用reuse storage;   truncate会将高水线复位

下面对两种操作对比

SQL> analyze table t estimate statistics;

Table analyzed.

SQL> select segment_name,blocks from dba_segments where segment_name=upper('t');

SEGMENT_NAME                       BLOCKS

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

T                                      24

SQL> select table_name,blocks,empty_blocks from user_tables where table_name=upper('t');

TABLE_NAME                         BLOCKS EMPTY_BLOCKS

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

T                                      20            3

USER_TABLES.BLOCKS 列代表该表中曾经使用过得数据库块的数目，即水线。

注意：USER_TABLES.BLOCKS EMPTY_BLOCKS (20+3=23)比DBA_SEGMENTS.BLOCKS少一个数据库块，这是因为有一个数据库块被保留用作表头。DBA_SEGMENTS.BLOCKS 表示分配给这个表的所有的数据库块的数目。USER_TABLES.BLOCKS表示已经使用过的数据库块的数目(水线)。

Delete删除表,

SQL> delete from t;

10000 rows deleted

SQL> commit;

Commit complete.

SQL> analyze table t estimate statistics;

Table analyzed.

SQL> select table_name,blocks,empty_blocks from user_tables where table_name=upper('t');

TABLE_NAME                         BLOCKS EMPTY_BLOCKS

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

T                                      20            3

Truncate截断表

SQL> truncate table t;

Table truncated.

SQL> analyze table t estimate statistics;

Table analyzed.

SQL> select table_name,blocks,empty_blocks from user_tables where table_name=upper('t');

TABLE_NAME                         BLOCKS EMPTY_BLOCKS

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

T                                       0            7

可见,delete表,BLOCK(高水线)不变,而truncate表BLOCKS(高水线)变为0

现在我们也看到blocks+empty_blocks=7,也就是oracle分配区时默认一次7+1(表头)=8个blocks;

高水线的作用: HWM对数据库的操作有如下影响：

a) 全表扫描通常要读出直到HWM标记的所有的属于该表数据库块，即使该表中没有任何数据。

b) 即使HWM以下有空闲的数据库块，键入在插入数据时使用了append关键字，则在插入时使用HWM以上的数据块，此时HWM会自动增大。

因此高水线是oracle优化时一个重要的参数

3.空间

既然高水线用来说明已经有多少数据块分配给这个表，那么高水线也可理解为表的空间占用。

即使delete将表中的数据全部删除，HWM还是为原值，所以还有那么多的空间分配给这个表，即它的空间还没有回收，

而truncate表后高水线变为0，那现在它就表示没有分配空间，即它的空间被回收了。

4．效率

要想查看delete,truncate那个效率更高，先构建一个大表，然后查看它们分别对些表删除所需的时间。

有个相当形象的比喻：领导给你两本书让你扔掉,delete就是你守在复印机前,把书一页页撕下来复印一份,再一页页扔到垃圾桶里,truncate就是直接把两本书扔到垃圾桶里，那个快那个慢不言而喻。

先在表中插入100000条记录，并打开时间

SQL> set timing on;

SQL> begin

  2  for i in 1..100000 loop

  3  insert into t values('10');

  4  commit;

  5  end loop;

  6  end;

  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:01:12.50

Delete删除表

SQL> delete from t;

100000 rows deleted.

Elapsed: 00:00:20.09

Truncate 截断表

#先把表回滚

SQL> rollback;

Rollback complete.

Elapsed: 00:00:17.36

SQL> select count(*) from t;

  COUNT(*)

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

    100000

Elapsed: 00:00:00.01

SQL> truncate table t;

Table truncated.

Elapsed: 00:00:00.20

可见删除同一个大小的表，delete用了20.09秒，而truncate只用了0.2秒.

 

 

 

 

 

 

 

 

truncate操作同没有where条件的delete操作十分相似。
1、无论truncate大表还是小表速度都非常快。delete要产生回滚信息来满足回滚需求，而truncate是不产生的。
2、truncate是DDL语句进行隐式提交，不能进行回滚操作。
3、truncate重新设定表和索引的HWM（高水标记），由于全表扫描和索引快速扫描都要读取所有的数据块知道HWM为止。所以全表扫描的性能不会因为delete而提高，但是经过truncate操作后速度会很快。
4、truncate不触发任何delete触发器。
5、不能赋给某个用户truncate其它用户表的权限。如果需要trucate其它用户表的权限必须对该用户赋DROP ANY TABLE权限。
6、当表被truncate后，这个表和索引所占用的空间会恢复到初始大小，而delete操作不会减少表或索引所占用的空间。
7、不能truncate一个带有外键的表，如果要删除首先要取消外键，然后再删除。

TRUNCATE和DELETE有以下几点区别

1、TRUNCATE在各种表上无论是大的还是小的都非常快。如果有ROLLBACK命令DELETE将被撤销，而TRUNCATE则不会被撤销。

2、TRUNCATE是一个DDL语言，向其他所有的DDL语言一样，他将被隐式提交，不能对TRUNCATE使用ROLLBACK命令。

3、TRUNCATE将重新设置高水平线和所有的索引。在对整个表和索引进行完全浏览时，经过TRUNCATE操作后的表比DELETE操作后的表要快得多。

4、TRUNCATE不能触发任何DELETE触发器。

5、不能授予任何人清空他人的表的权限。

6、当表被清空后表和表的索引讲重新设置成初始大小，而delete则不能。

7、不能清空父表。 TRUNCATE TABLE (schema)table_name DROP(REUSE) STORAGE 在默认是 DROP STORAGE 当使用DROP STORAGE时将缩短表和表索引，将表收缩到最小范围，并重新设置NEXT参数。REUSE STORAGE不会缩短表或者调整NEXT参数在特殊情况下使用 REUSE ST

DELETE语句执行删除的过程是每次从表中删除一行，并且同时将该行的的删除操作作为事务记录在日志中保存以便进行进行回滚操作。
TRUNCATE TABLE 则一次性地从表中删除所有的数据页并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。

TRUNCATE 是将你的表初始化。假如现在你的表中有10条数据，现在TRUNCATE TABLE 的话，就会将表还原为你最初刚创建好时的状态。表刚创建好时肯定是没有任何数据。因此数据就都没有了。

DELETE TABLE虽然也将你表中的数据清除，但是在你的事务日志文件（LDF）里，还会有相应的记录。没有彻底删除。并且DELETE TABLE 还可以删除指定列，比如我想删除ID为1的列。就可以写: delete 表名 where id=1

简单说：

TRUNCATE 删除数据表的结构

DELETE 删除数据表的数据 结构还在

如为字段设置了标识列 identity(1,1)

TRUNCATE 删除数据后 再添加数据 就从1开始自增

DELETE 删除数据后 再添加数据 原来的使用的就不能再用了 也就是不是从1开始了 因为原来的占用了