truncate 和 delete 的性能对比

truncate 和 delete 的性能对比

下面的例子将会使用不同的数据量来对比，delete 和 truncate 的性能。

一、百来条记录的表，delete 和 truncate 性能的对比。

SQL> set timing on

SQL> select count(*) from merge_01;

  COUNT(*)

----------

       107

Elapsed: 00:00:00.00

SQL> delete from merge_01;

107 rows deleted.

Elapsed: 00:00:00.03

SQL> rollback;

Rollback complete.

Elapsed: 00:00:00.00

SQL> truncate table merge_01;

Table truncated.

Elapsed: 00:00:00.00

二、只有一条记录的表，delete 和 truncate 性能的对比。

SQL> truncate table merge_01;

Table truncated.

SQL> insert into merge_01 select employee_id,first_name,last_name,salary from hr.employees

  2  where employee_id = 100;

1 row created.

Elapsed: 00:00:00.00

SQL> commit;

Commit complete.

Elapsed: 00:00:00.00

SQL> delete from merge_01;

1 row deleted.

Elapsed: 00:00:00.05

SQL> truncate table merge_01;

Table truncated.

Elapsed: 00:00:00.01

三，300多万条记录的表，delete 和truncate 性能的对比。

SQL> select count(*) from allobjects;

  COUNT(*)

----------

   3375996

Elapsed: 00:00:11.82

SQL> delete from allobjects;

3375996 rows deleted.

Elapsed: 00:02:50.97

SQL> rollback;

Rollback complete.

Elapsed: 00:06:40.29

SQL> truncate table allobjects;

Table truncated.

Elapsed: 00:00:01.74

上面的实例中的表都没有其他的依赖对象，没有索引等，相对单纯。

但是已经可以反映出truncate 和 delete 性能上面的差别。在只考虑性能方面

truncate 优于delete.

究竟是什么原因导致了delete 和 truncate 性能方面的差异？因为他们不是同一个

东西，各种移除掉表中所有行的方式不同。delete 处理问题比truncate 谨慎多了

需要把要删除的记录copy 进 undo tablespace 以便在需要的时候用于rollback,

提供读一致性，数据库恢复，并会生成大量的redo 信息，这些都需要时间，特别

是要进行I/O 操作。而truncate 就干脆多了，直接一笔把某张表中的人口（记录）

咔嚓掉，降低HWM。 

SQL> select count(*) from alltest01;

  COUNT(*)

----------

   3375996

SQL> delete from alltest01;

3375996 rows deleted.

Elapsed: 00:02:20.59

Execution Plan

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

Plan hash value: 3293322673

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

| Id  | Operation          | Name      | Rows  | Cost (%CPU)| Time     |

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

|   0 | DELETE STATEMENT   |           |  3377K| 13023   (1)| 00:02:37 |

|   1 |  DELETE            | ALLTEST01 |       |            |          |

|   2 |   TABLE ACCESS FULL| ALLTEST01 |  3377K| 13023   (1)| 00:02:37 |

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

Statistics

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

       2095  recursive calls

    3671312  db block gets

      95992  consistent gets

      47957  physical reads

 1256201104  redo size

        694  bytes sent via SQL*Net to client

        594  bytes received via SQL*Net from client

          3  SQL*Net roundtrips to/from client

          3  sorts (memory)

          0  sorts (disk)

    3375996  rows processed

SQL> rollback;

Rollback complete.

Elapsed: 00:07:53.56

SQL> rollback;

Rollback complete.

Elapsed: 00:07:53.56

SQL> truncate table alltest01;

Table truncated.

Elapsed: 00:00:01.63

但是是否在任何情况下truncate 的性能都优于delete 呢？

SQL> select count(*) from alltest02;

  COUNT(*)

----------

   3375996

下面测试一下有索引的情况。各种数据量下delete 和 truncate 的性能区别。

只有几条记录的小表，并且表中有索引存在。

SQL> create table ttt (x int primary key);

Table created.

SQL> insert into ttt values(1);

1 row created.

SQL> insert into ttt values(2);

1 row created.

SQL> commit;

Commit complete.

SQL> select index_name

  2  from user_indexes

  3  where table_name = 'TTT';

INDEX_NAME

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

SYS_C007425

SQL> set timing on

SQL> delete from ttt;

2 rows deleted.

Elapsed: 00:00:00.00

SQL> truncate table ttt;

Table truncated.

Elapsed: 00:00:00.09

可见在这种情况下truncate 的性能没有delete 来的高。

改变表中的数据量，再进行测试。现在表中有1000条记录。

SQL> begin

  2  for i in 1..1000

  3     loop

  4     insert into ttt values(i);

  5     end loop;

  6  end;

  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.24

SQL> commit;

Commit complete.

Elapsed: 00:00:00.00

SQL> delete from ttt;

1000 rows deleted.

Elapsed: 00:00:00.19

SQL> rollback;

Rollback complete.

Elapsed: 00:00:00.18

SQL> truncate table ttt;

Table truncated.

Elapsed: 00:00:00.01

下面加大数据量到百万级，在进行delete 和truncate 的测试。

SQL> begin

  2    for i in 1..2000000

  3    loop

  4    insert into ttt values(i);

  5    end loop;

  6  end;

  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:06:35.56

SQL> delete from ttt;

2000000 rows deleted.

Elapsed: 00:07:37.54

SQL> rollback;

Rollback complete.

Elapsed: 00:14:19.66

SQL> truncate table ttt;

Table truncated.

Elapsed: 00:00:00.45

当数据量很大的时候，delete 和truncate 的性能有天壤之别，truncate 在性能上面

完全可以胜过delete。

貌似truncate 的性能总是超过delete 的，除了有索引存在的时候，只有几条记录的情况

总是这样吗？还是偶然，我们再来试试。

SQL> insert into ttt values(2);

1 row created.

Elapsed: 00:00:00.00

SQL> select count(*) from ttt;

  COUNT(*)

----------

         2

Elapsed: 00:00:00.00

SQL> commit;

Commit complete.

Elapsed: 00:00:00.00

SQL> delete from ttt;

2 rows deleted.

Elapsed: 00:00:00.01

SQL> rollback;

Rollback complete.

Elapsed: 00:00:00.00

SQL> truncate table ttt;

Table truncated.

Elapsed: 00:00:00.04

在只有几条记录的时候由索引的表delete 的性能还是好于truncate。如果弃掉索引呢？

truncate 的性能总是好于delete 吗？我们再来测试。虽然前面我们已经测试过了，但

毕竟只测试了一次，过程就不写出来了，你会发现在没有索引的情况下只有几条记录的

情况下delete 和truncate 的性能可以说是相等的。在这种情况下看不出那种方式性能

会比较好。但是在数据量稍微大点，或者超大的时候无论有没有索引，truncate 的性能

都会比delete 好，特别是数据量很大的时候，这种区别更是明显。当然实际的情况远远比

上面说的复杂的多。

注：只有几条记录的表truncate 花费的时间比delete 多个人认为要

分情况，如果有索引存在切实是这样的，若没有索引两者分辨不出执行时间的差异。

我本想启用10046 事件进行跟踪，其实也说明不了什么，如果要本质的区分delete 和truncate

的性能，需要对oracle的原理有深厚的了解，特别是存储和io那一块的知识。

小结：我们可以说truncate 的性能在大部分情况下都是好于delete 的，或许

我们应该换一个角度，truncate 和delete 本来就是不同的干活，不要总是拿来比较，面对

不同的场景使用合适的方式或许是最好的答案。如果你在实际中遇到truncate 比delete慢

很多，以下的一些链接也许可以提供给你所需要的信息。

ref:

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2233415700346799621

http://datawarehouse.ittoolbox.com/groups/technical-functional/data-admin-l/truncate-takes-long-time-2875958

http://www.dbasupport.com/forums/showthread.php?t=49633

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

转载于:http://blog.itpub.net/26110315/viewspace-729870/