注意:這裡說的delete是指不帶where子句的delete語句

相同點:truncate和不帶where子句的delete, 以及drop都會刪除表內的資料

不同點:
1. truncate和 delete只刪除資料不刪除表的結構(定義)
drop語句將刪除表的結構被依賴的約束(constrain),觸發器(trigger),索引(index); 依賴於該表的儲存過程/函式將保留,但是變為invalid狀態.

2.delete語句是DML,這個操作會放到rollback segement中,事務提交之後才生效;如果有相應的trigger,執行的時候將被觸發.
truncate,drop是DDL, 操作立即生效,原資料不放到rollback segment中,不能回滾. 操作不觸發trigger.

3.delete語句不影響表所佔用的extent, 高水線(high watermark)保持原位置不動
顯然drop語句將表所佔用的空間全部釋放
truncate 語句預設情況下見空間釋放到 minextents個 extent,除非使用reuse storage; truncate會將高水線復位(回到最開始).

4.速度,一般來說: drop> truncate > delete

5.安全性:小心使用drop 和truncate,尤其沒有備份的時候.否則哭都來不及
使用上,想刪除部分資料行用delete,注意帶上where子句. 回滾段要足夠大.
想刪除表,當然用drop
想保留表而將所有資料刪除. 如果和事務無關,用truncate即可. 如果和事務有關,或者想觸發trigger,還是用delete.
如果是整理表內部的碎片,可以用truncate跟上reuse stroage,再重新匯入/插入資料

6：delete是DML語句，不會自動提交。

drop，truncate都是DDL語句，執行後會自動提交

7：truncate 只能對TABLE

　delete 可以是table,view,synonym

8：TRUNCATE TABLE 的物件必須是本模式下的，或者有drop any table的許可權，而DELETE 則是物件必須是本模式下的，或被授予 DELETE ON SCHEMA.TABLE 或DELETE ANY TABLE的許可權。

# delete/truncate 只刪除資料不刪除表，索引的結構。
drop 將刪除表的結構被依賴的 index/constrain/trigger，依賴於該表的 procedure/function 將保留，但是變為 invalid 狀態。
#

delete 是 dml，寫 rollback segement，可回滾，速度慢，事務提交之後才生效。在 9i 滿足 undo_retention 條件下可使用 flashback。一次性大批次資料的 delete 可能導致回滾段急劇擴充套件從而影響到資料庫，慎用。觸發 trigger。
truncate/drop 是 ddl，隱式提交，不寫 rollback segment，不能回滾，速度快。9i 不能使用 flashback。不觸發 trigger。
#

delete 不影響表所佔用的 extent，HWM 保持原位置不動，即使刪除的是最靠近 HWM 的資料。delete 其實也可以釋放空間，但是不降低 HWM，delete 後 block 的空閒空間達到 pct_used，就可以重用。
truncate 預設情況下將空間（表和索引）釋放到 minextents 個 extent，除非使用 reuse storage。truncate 會將高水線復位（回到最開始）。
drop 將表所佔用的空間全部釋放，segment 不存在，無所謂 HWM 的概念。
#

truncate/drop 的物件必須是本模式下的，或者被授予 drop any table 的許可權，但 drop any table 許可權不能 truncate/drop sys 的表。
delete 的物件必須是本模式下的，或者被授予 delete on SCHEMA.table 或 delete any table 的許可權，但 delete any table 許可權不能 delete sys 的表。
#

不能 truncate 一個帶有 enable 外來鍵的表，不管表裡有沒有資料，如果要 truncate，首先要 disable 外來鍵或者刪除外來鍵（drop 外來鍵的表肯定是刪除了外來鍵）。
不能 drop 一個帶有 enable 外來鍵的表，不管表裡有沒有資料，如果要 drop，首先要刪除外來鍵，或者直接用 drop table TABLE_NAME cascade constraints; 級聯刪除外來鍵。
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 不能用於參與了索引檢視的表。

---------------------------------
上面是sql聯機叢書上說的，似乎很少有書提到truncate table，也似乎很少用到它。今天看到一個問題則正是需要它。

問題：有一個擁有1億條資料的表，只需要保留其中的5條，其他刪除，如何做？

這就需要用truncate table來搞定了，如下：
select 5條資料 into #臨時表 from 1億條資料的牛X表

truncate table 1億條資料的牛X表
--讓它牛X，不到10毫秒幹掉它。

insert 1億條資料的牛X表 select * from #臨時表

drop table #臨時表

[轉][資料庫SQL語句]truncate/delete/drop同與不同

相關文章