delete，truncate和drop的區(qū)別

屠夫吧

delete，truncate和drop的區(qū)別
1、truncate和delete只刪除數(shù)據(jù)不刪除表的結(jié)構(gòu)(定義)，而drop語(yǔ)句將刪除表的結(jié)構(gòu)被依賴的約束(constrain)，觸發(fā)器(trigger)，索引(index); 依賴于該表的存儲(chǔ)過(guò)程/函數(shù)將保留，但是變?yōu)閕nvalid狀態(tài)。
2、delete語(yǔ)句是dml，這個(gè)操作會(huì)放到rollback segement中，事務(wù)提交之后才生效;如果有相應(yīng)的trigger，執(zhí)行的時(shí)候?qū)⒈挥|發(fā)。
3、truncate，drop是ddl，操作立即生效，原數(shù)據(jù)不放到rollback segment中，不能回滾，操作不觸發(fā)trigger。
4、delete語(yǔ)句不影響表所占用的extent，高水線(high watermark)保持原位置不動(dòng)，truncate 語(yǔ)句缺省情況下將數(shù)據(jù)空間釋放，除非使用reuse storage; truncate會(huì)將高水線復(fù)位(回到最開(kāi)始)，drop語(yǔ)句將表所占用的空間全部釋放。
5、語(yǔ)句執(zhí)行速度，一般來(lái)說(shuō): drop> truncate > delete
6、小心使用drop 和truncate，尤其沒(méi)有備份的時(shí)候，想刪除部分?jǐn)?shù)據(jù)行用delete，注意帶上where子句。
7、想保留表而將所有數(shù)據(jù)刪除，如果和事務(wù)無(wú)關(guān)，用truncate即可，如果和事務(wù)有關(guān)，或者想觸發(fā)trigger，還是用delete。
8、如果是整理表內(nèi)部的碎片，可以用truncate跟上reuse stroage，再重新導(dǎo)入/插入數(shù)據(jù)。
9、對(duì)于由FOREIGN KEY 約束引用的表，不能使用TRUNCATE TABLE，而應(yīng)使用不WHERE子句的DELETE語(yǔ)句。

另外，如果沒(méi)有備份，drop，delete，truncate是否能夠恢復(fù)？
從直觀的感覺(jué)來(lái)說(shuō)，似乎這個(gè)問(wèn)題沒(méi)什么含量，既然都已經(jīng)drop，或者truncate了，是一個(gè)ddl語(yǔ)句，數(shù)據(jù)應(yīng)該是回不來(lái)了，如果是delete，并且做了commit，數(shù)據(jù)也應(yīng)該回不來(lái)了。
對(duì)于這個(gè)問(wèn)題，至少?gòu)腛racle的角度來(lái)說(shuō)，答案是可能，而且某些情況下是很可能。我們來(lái)一個(gè)一個(gè)分析：
首先是drop操作，在Oracle里面，默認(rèn)情況下是有回收站的功能，就是把一個(gè)表做了drop操作之后，其實(shí)從物理上這個(gè)表并沒(méi)有刪除，而是簡(jiǎn)單換了一個(gè)很長(zhǎng)的名字
如果回收站是開(kāi)啟的（默認(rèn)開(kāi)啟）
SQL> show parameter recyclebin
NAME       TYPE    VALUE
---------- ------- ------
recyclebin string  on
那么這個(gè)時(shí)候drop的表是否一定能夠恢復(fù)呢，還是不一定，為什么，主要有幾個(gè)原因可以考慮：
比如這個(gè)表所在的表空間資源緊張，很可能回收站里的這個(gè)表的數(shù)據(jù)就會(huì)被回收后存放其它的數(shù)據(jù)了；
或者說(shuō)某個(gè)用戶的配額（quota)本來(lái)就不足，這個(gè)時(shí)候也很可能使用回收站的資源就會(huì)受到限制；
或者說(shuō)在某些場(chǎng)景下，你創(chuàng)建的表直接放在了system表空間下，這個(gè)時(shí)候哪怕空間充足，也是不能直接恢復(fù)回來(lái)的。
所以通過(guò)這些分析來(lái)看，drop操作還是有很多的可能，但是還是最開(kāi)始的聲明，是可能而不是肯定。


再來(lái)看看truncate操作，這個(gè)操作就算從數(shù)據(jù)恢復(fù)的角度來(lái)說(shuō)，也是無(wú)能為力了。但是我們的答案還是可能，這個(gè)時(shí)候還是要說(shuō)說(shuō)flashback database這個(gè)特性了。
比如某個(gè)時(shí)間點(diǎn)truncate了一個(gè)很重要的表。我們可以在情況允許的情況下嘗試flashback database
當(dāng)然這個(gè)特性你也還是有一個(gè)系統(tǒng)級(jí)的設(shè)置，默認(rèn)情況下，flashback database的特性是沒(méi)有啟用的
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
需要在數(shù)據(jù)庫(kù)mount階段使用alter database flashback on來(lái)啟用，因?yàn)殚W回日志記錄數(shù)據(jù)庫(kù)級(jí)的一些數(shù)據(jù)變化，所以勢(shì)必會(huì)對(duì)數(shù)據(jù)庫(kù)的性能和資源造成一定的影響。不過(guò)在測(cè)試環(huán)境，非正式環(huán)境還是可以大膽使用的。
比如我們也不太確定truncate的時(shí)間，我們可以不斷的閃回，直到找到滿意的結(jié)果未知
如果在某一個(gè)時(shí)間點(diǎn)我們發(fā)現(xiàn)數(shù)據(jù)是我們所期望的，我們就可以使用exp把數(shù)據(jù)給導(dǎo)出來(lái)，然后再需要的時(shí)候把數(shù)據(jù)給導(dǎo)進(jìn)去。

最后說(shuō)說(shuō)delete操作，這個(gè)從Oracle層面來(lái)說(shuō)，選項(xiàng)就更多了。
        delete操作會(huì)利用undo的資源，生成相應(yīng)的undo_sql，如果我們delete了數(shù)據(jù)之后做了commit，此時(shí)我們可以考慮flashback table這個(gè)特性：
flashback table test to timestamp to_timestamp('2015-08-01:12:02:27'，'yyyy-mm-dd:hh24:mi:ss');
我們也可以把那個(gè)時(shí)間點(diǎn)的數(shù)據(jù)都查出來(lái)：
select * from test as of timestamp  to_timestamp('2015-08-01:12:02:27'，'yyyy-mm-dd:hh24:mi:ss');
然后再創(chuàng)建一個(gè)表，把查詢出來(lái)的數(shù)據(jù)導(dǎo)入進(jìn)去，這樣數(shù)據(jù)就恢復(fù)啦。
更多精彩Oracle學(xué)習(xí)，請(qǐng)關(guān)注我：

yanglixing

8、如果是整理表內(nèi)部的碎片，可以用truncate跟上reuse stroage，再重新導(dǎo)入/插入數(shù)據(jù)。

從正式環(huán)境導(dǎo)出來(lái)的數(shù)據(jù)庫(kù)（有些表保留數(shù)據(jù)，有些是無(wú)數(shù)據(jù)導(dǎo)出），做了幾筆業(yè)務(wù)，表中有幾條數(shù)據(jù)，然后表空間接近100%，是怎么回事？

表空間大小正常（即分配的表空間不是那種特別小，只能存放幾條數(shù)據(jù)的）