ITPub博客

首页 > Linux操作系统 > Linux操作系统 > truncate 和 delete 的性能对比

truncate 和 delete 的性能对比

原创 Linux操作系统 作者:pingley 时间:2012-05-15 22:43:43 0 删除 编辑
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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 关于oracle commit
请登录后发表评论 登录
全部评论

注册时间:2012-02-06

  • 博文量
    169
  • 访问量
    715332