Oracle中truncate和delete的區別(例項）

TRUNCATE TABLE 在功能上與不帶 WHERE 子句的 DELETE 語句相同：二者均刪除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系統和事務日誌資源少。 

DELETE 語句每次刪除一行，並在事務日誌中為所刪除的每行記錄一項。TRUNCATE TABLE 透過釋放儲存表資料所用的資料頁來刪除資料，並且只在事務日誌中記錄頁的釋放。

TRUNCATE TABLE 刪除表中的所有行，但表結構及其列、約束、索引等保持不變。新行標識所用的計數值重置為該列的種子。如果想保留標識計數值，請改用 DELETE。如果要刪除表定義及其資料，請使用 DROP TABLE 語句。

對於由 FOREIGN KEY 約束引用的表，不能使用 TRUNCATE TABLE，而應使用不帶 WHERE 子句的 DELETE 語句。由於 TRUNCATE TABLE 不記錄在日誌中，所以它不能啟用觸發器。 

TRUNCATE TABLE 不能用於參與了索引檢視的表。

truncate操作與delete操作對比

操作	回滾	高水線	空間	效率
Truncate	不能	下降	回收	快
delete	可以	不變	不回收	慢

下面分別用例項檢視它們的不同

首先要明白兩點

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操作沒有提交)

所有的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,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秒

既然高水線用來說明已經有多少資料塊分配給這個表，那麼高水線也可理解為表的空間佔用。

即使delete將表中的資料全部刪除，HWM還是為原值，所以還有那麼多的空間分配給這個表，即它的空間還沒有回收，

而truncate表後高水線變為0，那現在它就表示沒有分配空間，即它的空間被回收了。