【DB筆試面試476】DELETE、DROP和TRUNCATE的區別是什麼？
- 2019 年 10 月 11 日
- 筆記
題目部分
DELETE、DROP和TRUNCATE的區別是什麼？
答案部分
DELETE、DROP和TRUNCATE的異同點如下表所示：
表 3-9 DELETE、DROP和TRUNCATE的區別
|
相同點 |
1、TRUNCATE和不帶WHERE子句的DELETE及DROP都會刪除表內的所有數據2、DROP和TRUNCATE都是DDL語句，執行後會自動提交3、表上的索引大小會自動進行維護 |
|||
|---|---|---|---|---|
|
不同點 |
分類 |
DROP |
TRUNCATE |
DELETE |
|
是否刪除表結構 |
刪除表結構及其表上的約束，且依賴於該表的存儲過程和函數等將變為INVALID狀態 |
只刪除數據不刪除表的定義、約束、觸發器和索引 |
||
|
SQL命令類型 |
DDL語句，隱式提交，不能對TRUNCATE和DROP使用ROLLBACK命令 |
DML語句，事務提交（COMMIT）之後才生效，可以使用ROLLBACK語句撤銷未提交的事務 |
||
|
刪除的數據是否放入回滾段（ROLLBACK SEGMENT） |
否 |
否 |
是 |
|
|
高水位是否下降 |
是 |
是，在宏觀上表現為TRUNCATE操作後，表的大小變為初始化的大小 |
否，在宏觀上表現為DELETE後表的大小並不會因此而改變，所以，在對整個表進行全表掃描時，經過TRUNCATE操作後的表比DELETE操作後的表要快得多 |
|
|
日誌的產生 |
少量日誌 |
少量日誌 |
大量日誌 |
|
|
是否可以通過閃回查詢來找回數據 |
否 |
否 |
是 |
|
|
是否可以對視圖進行操作 |
是 |
否 |
是 |
|
|
級聯刪除 |
不能DROP一個帶有ENABLE外鍵的表 |
不能TRUNCATE一個帶有ENABLE外鍵的表，會報錯ORA-02266 |
可以DELETE一個帶有ENABLE外鍵的表 |
|
|
執行速度 |
一般來說，DROP>TRUNCATE>DELETE，DROP和TRUNCATE由於是在底層修改了數據字典，所以，無論是大表還是小表執行都非常快，而DELETE是需要讀取數據到Undo，所以，對於大表進行DELETE全表操作將會非常慢 |
|||
|
安全性 |
DROP和TRUNCATE在無備份的情況下需謹慎 |
|||
|
使用方面 |
想刪除部分數據行只能用DELETE且帶上WHERE子句；想刪除表數據及其結構則使用DROP；想保留表結構而將所有數據刪除則使用TRUNCATE |
|||
|
恢復方法 |
使用回收站恢復，閃回數據庫，RMAN備份、DUL工具等 |
閃回數據庫，RMAN備份、DUL工具等 |
閃回查詢、閃回事務、閃回版本、閃回數據庫等 |
|
DELETE和TRUNCATE都可以用來刪除表中所有的記錄。但二者的不同之處主要體現在以下幾個方面的內容：
（1）TRUNCATE是一個DDL語句，DELETE是DML語句。
（2）TRUNCATE將被隱式提交，不能對TRUNCATE使用ROLLBACK命令，而對DML語句可以執行ROLLBACK命令來撤銷未提交的事務。
（3）TRUNCATE由於是在底層修改了數據字典，所以在各種表上無論是大表的還是小表操作都非常快。DELETE操作需要寫日誌，而TRUNCATE不需要寫日誌，所以對於大表進行DELETE全表操作非常慢。
（4）TRUNCATE將重置表的高水位線，但DELETE不會重置表的高水位線，在宏觀上表現為TRUNCATE操作後，表的大小變為初始化的大小，而DELETE後表的大小並不會因此而改變，所以在對整個表進行全表掃描時，經過TRUNCATE操作後的表比DELETE操作後的表要快得多，另外需要注意的是，無論TRUNCATE還是DELETE操作，表上的索引都會自動維護。
（5）TRUNCATE操作後的表不能通過閃回特性來找回，但DELETE後的表可以通過閃回特性來找回數據。
（6）TRUNCATE不能加條件刪除數據；DELETE可以加條件刪除。
（7）不能TRUNCATE一個帶有ENABLE外鍵的表，會報錯ORA-02266。可以DELETE一個帶有ENABLE外鍵的表。
本文選自《Oracle程序員面試筆試寶典》，作者：李華榮。
About Me：小麥苗
● 本文作者：小麥苗，只專註於數據庫的技術，更注重技術的運用
● 作者博客地址：http://blog.itpub.net/26736162/abstract/1/
● 本系列題目來源於作者的學習筆記，部分整理自網絡，若有侵權或不當之處還請諒解
● 版權所有，歡迎分享本文，轉載請保留出處
● 題目解答若有不當之處，還望各位朋友批評指正，共同進步
