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

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

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

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

| 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:

truncate 和 delete 的效能對比

相關文章