Truncate Delete Drop 的區別以及 高水位HWM

truncate操作與delete操作對比

操作	復原	高水線	空間	效率
Truncate	不能	下降	回收	快
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,再重新匯入/插入資料

DELETE   語句每次刪除一行，並在交易記錄中為所刪除的每行記錄一項。

TRUNCATE   TABLE   通過釋放儲存表資料所用的資料頁來刪除資料，並且只在交易記錄中記錄頁的釋放。 

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

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

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

 

 

PS:附加說明兩個知識點：復原 與 高水位

1、  復原

1.在Oracle 中資料刪除後還能復原是因為它把未經處理資料放到了undo資料表空間,

2.DML語句使用undo資料表空間,DDL語句不使用undo,而delete是DML語句,truncate是DDL語句,別外DDL語句是隱式提交.所以truncate操用不能復原,而delete操作可以.

2、  高水位

所有的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表示已經使用過的資料庫塊的數目(水線)。

1 2 下一頁