深度分析DROP,TRUNCATE与DELETE的区别【我的数据库之路系列】
轉載自：http://hi.baidu.com/bjn_wuming/blog/item/8b27a9af36ef26f6faed5077.html
?? 很久不發文章了，實際上前2個星期有點忙的過頭了，現在正處于前一個需求剛結束，后一個還沒來的真空休閑期，早就想要發點東西，但是光研究DUMP就花了我2天半。。下面正題
-----------------------------------------------------------------------------------------------------
這次特意從數據庫塊的層次深度分析一下DROP,TRUNCATE和DELETE的區別：
???? 在此之前先為那些初學數據庫的童鞋們闡述2個概念，如果你還沒有真正理解這2個概念那也請你看完：DML語句與DDL語句。
簡單的DML與DDL概念：
???? 我們不去重復那些抽象又沒用的概念，簡單的說DML語句就是增刪改(INSERT,DELETE,UPDATE),DDL就是對數據庫對象操作的語句(CREATE,DROP,TRUNCATE)等，何為數據庫對象：數據庫商們將表，索引，視圖，同義詞等都稱為數據庫對象。所以區分DDL和DML的方法其實在語句字面上就可以看到，DDL語句都是在動作+對象的格式，比如great是動作，說明要創建什么，table就是對象，所以create table就是DDL，其他的DDL同樣，沒注意過的可以去看一看。DML語句則是動作+具體的對象名，比如insert into + 表明等，相信大家從來也沒有見過insert table,update table這樣的寫法吧？我甚至還能肯定很多人曾因寫出delete table這樣的語法而被報錯。
深度的DML與DDL概念：
???? 如果你有數據庫體系結構的基礎，相信你一定不會對上面提出的那個概念感興趣，那么這里就給你一個真正的DML與DDL的概念：
?????
???? 所有對數據文件中的數據的操作就稱為DML語句，所有對數據字典表的操作為DDL語句。
?????
???? 何為數據字典表？你可以去網上查一查研究一下，我只給以個略通俗的解釋，數據庫在啟動的時候會先連接上數據字典表，這些表（不是一張）里存放了上面說的數據庫對象的基本信息，比如表的表名，表對應的存放數據的數據文件的名字和在操作系統中的具體位置等等，我們平時所寫的查詢語句就是數據庫先通過數據字典表找到SQL中的表的位置，然后才能從中查詢數據，也就是說，如果數據字典表中沒有你建的表的信息，那么你的SQL就不可能查到這張表，就會報出找不到表的錯誤。
????
???? 注意上面給出的數據字典的概念很重要，理解了才能真正明白后面的結論。
PS：SELECT語句不屬于DML和DDL語句，它是DQL語句，看名字就知道了，其中的Q是QUERY查詢的意思，但有人也喜歡把它分到DML里，理論上說它確實也是對數據的操作。
???? 雖然能夠查到這個文章的各位應該都知道DROP，TRUNCATE和DELETE的效果是什么，但還是稍微解釋一下，效果上就是TRUNCATE和DELETE都可以把數據刪除，TRUNCATE只能一次刪除所有數據且不能回滾，DELETE則可以選擇性的刪數據，刪除之后如果后悔還有一次回滾不提交結果的機會。而DROP當然是直接刪表（或者其他對象）。
????????
????接下來就是重點了，我在這里直接給出核心結論，看完結論后如果對后面的測試過程部分實在不感興趣的童鞋們就可以不用看了：
??????????
???? TRUNCATE和DROP是不真正刪除表中數據的，也就是說即便你用TRUNCATE達到了刪數據的效果，甚至用DROP看似刪掉了那張表，其中的數據實際上還存在于數據文件上，那些數據會在下次新的數據進來的時候被覆蓋掉。
??????????
???? DELETE也不是立刻就刪除數據的，但它對數據文件中的數據的標志位做個一個改動(添加了一個‘D’)，這些數據就代表被刪除了，下次數據塊重組的時候就會被刪掉。
??????????
???? 解釋以上問題的原因就在于剛才提到的，DROP和TRUNCATE是DDL語句，它們只對數據字典表中的關于這個表的某個值做了改動，所以如果你愿意實驗下的話就會發現，無論數據有幾億幾十億幾百億，DROP和TRUNCATE的速度都非常快，原因就是他們只需要改數據字典表，不會去動實際的數據。
??????????
???? 之后關于TRUNCATE修改高水位線的問題大家想必也就清楚了，高水位線記錄了最后一條數據在磁盤中的物理位置(地址)，這個地址就存在數據字典表中，TRUNCATE只是去修改這個值，而DROP是刪除數據字典表中與該表有關的幾條數據。
???? 下面就以oracle數據庫為例，來讓我們來用測試證明DDL確實沒有對數據進行任何改動，在這里我只使用TRUNCATE和DELETE證明，DROP和TRUNCATE是一樣的所以不再做重復實驗：
???????
首先，為了方便測試，我創建了一個比較小的表空間，注意只能在授權用戶或是DBA用戶下創建：
create tablespace ttt??
logging????????????????????????????????????????????????????? --創建重做日志，反之則寫成nologging
datafile 'D:\oracle\oradata\Oracle9i\user_data.dbf'??? --指定表空間的路徑和文件，文件會自動創建的
size 1m???????????????????????????? --指定表空間的初始大小，我們就用1M就夠了
autoextend on??????????????????????????????????????????????? --支持表空間自動擴展
next 1m maxsize 20m??????????????? --表空間擴展時每次擴展的空間大小以及最大空間上限
extent management local??????????????????????????????????? --規定區大小由系統自動確定
AUTO;?????????????????????????????????????????? --只能使用在本地管理的表空間中，由LOCAL管理表空間時，數據塊中的空閑空間增加或減少后，其新狀態都會在位圖中反映出來，以便更好的自動管理，對含LOB字段的表無效
表空間建好后，我們就可以在這個表空間上建表:
SQL> CREATE TABLE ttt(
2???????? a_id number,
3???????? b_char varchar2(100)
4 ) tablespace space_t;
表已創建。
這里說一個分支問題，有人會想通過數據字典表來查看表空間中的數據是否真的刪除或存在，但實際上是沒有效果的，數據字典表是從數據庫的角度去記錄表的信息，所以你TRUNCATE后就算數據存在，數據字典表依然會寫成不存在，比如用如下語句可以計算出表空間的使用情況，但不能說明數據是否還存在于數據文件中：
select
b.file_name 物理文件名,
b.tablespace_name 表空間,
b.bytes/1024/1024 大小M,
(b.bytes-sum(nvl(a.bytes,0)))/1024/1024 已使用M,
substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) 利用率
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_name,b.bytes
order by b.tablespace_name;
我們再回到正題，用下面的語句我們可以看到表空間所在的數據文件的ID號和你建的表的表頭處于第幾個塊，當然用上面那條SQL語句中的2個表也可以看到他們表空間所在的數據文件號：
select t.header_file,t.header_block from dba_segments t
where t.segment_name='TTT';??????????? --要特別注意里面的名字要大寫，數據字典表會把你的表名等信息都變成大寫存起來
HEADER_FILE HEADER_BLOCK
----------- ------------
????????? 6?????????? 19
我們看到數據文件號為6,表頭信息所在塊為第19個,但是，我們插入數據的時候不能確定數據就在第20塊上，因為ASSM會通過特定算法來決定你的數據存在哪個塊上，你可以通過數據的ROWID去算它的數據塊，當然也可以用土方法往后一個一個找，一般來說數據的數據塊應該不會離表頭所在塊很遠，比我新插的數據就在第21個上。怎么一個個的確定？要么用"segment management manual"將ASSM變成MSSM，要么就挨個DUMP吧。。后面我解釋怎么DUMP數據塊
插入數據：
SQL> INSERT INTO ttt VALUES(1,'sdkjaskdhksdhdsf');
已創建 1 行
SQL> INSERT INTO ttt VALUES(1,'sdkjaskdhksdhdsasdassadf');
已創建 1 行
SQL> commit;
提交完成
DUMP數據塊的語句如下，如果你想DUMP別的，語法去網上可以查到，我這里不解釋了：
SQL> alter system dump datafile 6 block 20;
系統已更改
看到上面的語句就是我剛才查出來的對應的文件號和存放表頭信息的塊的后一個塊，存表頭信息的塊一般是不存數據的。
DUMP結束后我們去對應的控制文件中找結果，以.trc結尾，別找錯了不是.dmp結尾的文件。
我的路徑在下面，你們的路徑可以根據我的大概找一找：
C:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_4984.trc
打開后里面會有這樣的信息：
*** 2010-07-02 14:26:21.484
Start dump data blocks tsn: 7 file#: 6 minblk 20 maxblk 20
buffer tsn: 7 rdba: 0x01800014 (6/20)
scn: 0x0000.003eeda7 seq: 0x01 flg: 0x00 tail: 0xeda70601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x07C12200 to 0x07C14200
7C12200 0000A206 01800014 003EEDA7 00010000 [..........>.....]
7C12210 00000000 00000001 0000D7A7 003EED64 [............d.>.]
7C12220 00000000 00320002 01800011 00000000 [......2.........]
7C12230 00000000 00000000 00000000 00000000 [................]
??????? Repeat 2 times
7C12260 00000000 00000000 000EFFFF 1F8A1F98 [................]
7C12270 00001F8A 00000000 00000000 00000000 [................]
7C12280 00000000 00000000 00000000 00000000 [................]
??????? Repeat 502 times
7C141F0 00000000 00000000 00000000 EDA70601 [................]
Block header dump: 0x01800014
Object id on Block? Y
seg/obj: 0xd7a7 csc: 0x00.3eed64 itc: 2 flg: E typ: 1 - DATA
???? brn: 0 bdba: 0x1800011 ver: 0x01 opc: 0
???? inc: 0 exflg: 0
Itl?????????? Xid????????????????? Uba???????? Flag Lck??????? Scn/Fsc
0x01?? 0x0000.000.00000000 0x00000000.0000.00 ----??? 0 fsc 0x0000.00000000
0x02?? 0x0000.000.00000000 0x00000000.0000.00 ----??? 0 fsc 0x0000.00000000
data_block_dump,data header at 0x7c12264
===============
tsiz: 0x1f98
hsiz: 0xe
pbl: 0x07c12264
bdba: 0x01800014
???? 76543210
flag=--------
ntab=0
nrow=0
frre=-1
fsbo=0xe
fseo=0x1f98
avsp=0x1f8a
tosp=0x1f8a
block_row_dump:
end_of_block_dump
End dump data blocks tsn: 7 file#: 6 minblk 20 maxblk 20
在我們現在的話題里大家不需要對上面的東西了解太多，注意看標紅的地方的nrow=0說明了這個塊是個空塊，我插入的數據沒在這個塊里，這就驗證上面說的插入的數據不一定緊跟在表頭之后，那我們就去DUMP下一個塊看看(好土的窮舉法-_-!)
步驟同上：
SQL> alter system dump datafile 6 block 21;
系統已更改。
*** 2010-07-02 14:28:56.203
Start dump data blocks tsn: 7 file#: 6 minblk 21 maxblk 21
buffer tsn: 7 rdba: 0x01800015 (6/21)
scn: 0x0000.003eeddc seq: 0x02 flg: 0x06 tail: 0xeddc0602
frmt: 0x02 chkval: 0xa204 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x07C12200 to 0x07C14200
7C12200 0000A206 01800015 003EEDDC 06020000 [..........>.....]
7C12210 0000A204 00000001 0000D7A7 003EED64 [............d.>.]
7C12220 00000000 00320002 01800011 00010006 [......2.........]
7C12230 00000536 0080215D 000104C2 00002001 [6...]!....... ..]
7C12240 003EEDA7 00030009 0000056C 00800343 [..>.....l...C...]
7C12250 003203B8 00002001 003EEDDC 00000000 [..2.. ....>.....]
7C12260 00000000 00020100 0016FFFF 1F4C1F62 [............b.L.]
7C12270 00001F4C 1F810002 00001F62 00000000 [L.......b.......]
7C12280 00000000 00000000 00000000 00000000 [................]
??????? Repeat 499 times
7C141C0 00000000 022C0000 02C10202 6B647318 [......,......sdk]
7C141D0 6B73616A 736B6864 73646864 61647361 [jaskdhksdhdsasda]
7C141E0 64617373 02012C66 1002C102 6A6B6473 [ssadf,......sdkj]
7C141F0 646B7361 64736B68 66736468 EDDC0602 [askdhksdhdsf....]
Block header dump: 0x01800015
Object id on Block? Y
seg/obj: 0xd7a7 csc: 0x00.3eed64 itc: 2 flg: E typ: 1 - DATA
???? brn: 0 bdba: 0x1800011 ver: 0x01 opc: 0
???? inc: 0 exflg: 0
Itl?????????? Xid????????????????? Uba???????? Flag Lck??????? Scn/Fsc
0x01?? 0x0006.001.00000536 0x0080215d.04c2.01 --U-??? 1 fsc 0x0000.003eeda7
0x02?? 0x0009.003.0000056c 0x00800343.03b8.32 --U-??? 1 fsc 0x0000.003eeddc
data_block_dump,data header at 0x7c12264
===============
tsiz: 0x1f98
hsiz: 0x16
pbl: 0x07c12264
bdba: 0x01800015
???? 76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f62
avsp=0x1f4c
tosp=0x1f4c
0xe:pti[0] nrow=2 offs=0
0x12:pri[0] offs=0x1f81
0x14:pri[1] offs=0x1f62
block_row_dump:
tab 0, row 0, @0x1f81
tl: 23 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 02
col 1: [16] 73 64 6b 6a 61 73 6b 64 68 6b 73 64 68 64 73 66
tab 0, row 1, @0x1f62
tl: 31 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 02
col 1: [24]
73 64 6b 6a 61 73 6b 64 68 6b 73 64 68 64 73 61 73 64 61 73 73 61 64 66
end_of_block_dump
End dump data blocks tsn: 7 file#: 6 minblk 21 maxblk 21
看一看上面的信息，我們從標紅的地方可以看出，數據在數據塊中以從后往前的形式確實存進去了，下面開始正式測試，為了節省字數，在下面的實驗過程中，我將省略掉標紅部分以外的信息。
首先我們從DELETE開始：
SQL> delete from ttt;
已刪除2行。
SQL> commit;
提交完成。
SQL> alter system dump datafile 6 block 21;
系統已更改。
7C141C0 00000000 013C0000 02C10202 6B647318 [......<......sdk]
7C141D0 6B73616A 736B6864 73646864 61647361 [jaskdhksdhdsasda]
7C141E0 64617373 02013C66 1002C102 6A6B6473 [ssadf<......sdkj]
7C141F0 646B7361 64736B68 66736468 EE950601 [askdhksdhdsf....]
...
nrow=2
...
block_row_dump:
tab 0, row 0, @0x1f81
tl: 2 fb: --HDFL-- lb: 0x1
tab 0, row 1, @0x1f62
tl: 2 fb: --HDFL-- lb: 0x1
end_of_block_dump
End dump data blocks tsn: 7 file#: 6 minblk 21 maxblk 21
注意看上面，DELETE后雖然在上面看數據還在，nrow顯示也為2行，但下面則對其做了修改，在原來--H-FL--的位置增加了一個標志位'D'，在其后面該行的信息也被清理了，這些就是標志信息，原本的數據則會在下次數據塊進行重組的時候清理掉，或者被滿足某些條件的新信息進入的時候覆蓋掉。
下面我們來看一下TRUNCATE，我再插入2條數據
SQL> INSERT INTO ttt VALUES(1,'aaaaaaaaaaaaaaaa');
已創建 1 行。
SQL> INSERT INTO ttt VALUES(1,'aaaaaaaaaaaaaaaa');
已創建 1 行。
SQL> commit;
提交完成。
SQL> alter system dump datafile 6 block 21;
系統已更改。
7C14190 00000000 00000000 0202022C 611002C1 [........,......a]
7C141A0 61616161 61616161 61616161 2C616161 [aaaaaaaaaaaaaaa,]
7C141B0 C1020202 61611002 61616161 61616161 [......aaaaaaaaaa]
7C141C0 61616161 013C6161 02C10202 6B647318 [aaaaaa<......sdk]
7C141D0 6B73616A 736B6864 73646864 61647361 [jaskdhksdhdsasda]
7C141E0 64617373 02013C66 1002C102 6A6B6473 [ssadf<......sdkj]
7C141F0 646B7361 64736B68 66736468 F2200603 [askdhksdhdsf.. .]
...
nrow=4
...
block_row_dump:
tab 0, row 0, @0x1f81
tl: 2 fb: --HDFL-- lb: 0x1
tab 0, row 1, @0x1f62
tl: 2 fb: --HDFL-- lb: 0x1
tab 0, row 2, @0x1f4b
tl: 23 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 02
col 1: [16] 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
tab 0, row 3, @0x1f34
tl: 23 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 02
col 1: [16] 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
end_of_block_dump
End dump data blocks tsn: 7 file#: 6 minblk 21 maxblk 21
DELETE的數據沒有被覆蓋掉有很多原因，其中上面提到的數據庫的高水位線問題就是其中之一，它記錄了最末尾的記錄的位置，所以新數據不會覆蓋原數據而是在其之后插入。
然后我們TRUNCATE一下看看：
SQL> truncate table ttt;
表被截斷。
SQL> alter system dump datafile 6 block 21;
系統已更改。
7C14190 00000000 00000000 0202022C 611002C1 [........,......a]
7C141A0 61616161 61616161 61616161 2C616161 [aaaaaaaaaaaaaaa,]
7C141B0 C1020202 61611002 61616161 61616161 [......aaaaaaaaaa]
7C141C0 61616161 013C6161 02C10202 6B647318 [aaaaaa<......sdk]
7C141D0 6B73616A 736B6864 73646864 61647361 [jaskdhksdhdsasda]
7C141E0 64617373 02013C66 1002C102 6A6B6473 [ssadf<......sdkj]
7C141F0 646B7361 64736B68 66736468 F2200603 [askdhksdhdsf.. .]
...
nrow=4
...
block_row_dump:
tab 0, row 0, @0x1f81
tl: 2 fb: --HDFL-- lb: 0x1
tab 0, row 1, @0x1f62
tl: 2 fb: --HDFL-- lb: 0x1
tab 0, row 2, @0x1f4b
tl: 23 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 02
col 1: [16] 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
tab 0, row 3, @0x1f34
tl: 23 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 02
col 1: [16] 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
end_of_block_dump
End dump data blocks tsn: 7 file#: 6 minblk 21 maxblk 21
看到了嗎?數據與之前沒任何變化，說明TRUNCATE沒有對數據做任何操作，但這時候你再查詢表中數據的時候會發現表是空的。
下面咱們再次插入一條：
SQL> INSERT INTO ttt VALUES(1,'bbbbbbbbbbbbbbb');
已創建 1 行。
SQL> commit;
提交完成。
SQL> alter system dump datafile 6 block 21;
系統已更改。
94A2200 0000A206 01800015 003EF72C 06010000 [........,.>.....]
94A2210 0000AE46 00000001 0000D7A8 003EF72A [F...........*.>.]
94A2220 00000000 00320002 01800011 002D0001 [......2.......-.]
94A2230 00000431 00800197 003101B6 00002001 [1.........1.. ..]
94A2240 003EF72C 00000000 00000000 00000000 [,.>.............]
94A2250 00000000 00000000 00000000 00000000 [................]
94A2260 00000000 00010100 0014FFFF 1F6E1F82 [..............n.]
94A2270 00001F6E 1F820001 00000000 00000000 [n...............]
94A2280 00000000 00000000 00000000 00000000 [................]
??????? Repeat 501 times
94A41E0 00000000 012C0000 02C10202 6262620F [......,......bbb]
94A41F0 62626262 62626262 62626262 F72C0601 [bbbbbbbbbbbb..,.]
...
nrow=1
...
block_row_dump:
tab 0, row 0, @0x1f82
tl: 22 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 02
col 1: [15] 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62
end_of_block_dump
End dump data blocks tsn: 7 file#: 6 minblk 21 maxblk 21
這次大家都看明白了吧，顯示的nrow從本來的4行變成了1行，TRUNCATE重置了高水位線，所以數據會從新插入并蓋掉了之前所有的數據
最后重復一下我們一開始提到的結論，truncate和drop都是DDL語句，他們只對數據字典表進行操作與實際數據無關，所以執行速度很快，原來的數據會在下一次使用這個塊的時候被覆蓋掉，
而DELETE是DML語句，它直接對數據進行操作，但并非立刻就刪除數據，而是先做上標志，之后在重組數據塊的時候清除，或者滿足某些條件（如數據塊填滿，高水位線重置等）的時候被新數據覆蓋掉。
轉載于:https://www.cnblogs.com/qiangqiang/archive/2010/09/19/1831104.html
總結
以上是生活随笔為你收集整理的深度分析DROP,TRUNCATE与DELETE的区别【我的数据库之路系列】的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 备份的误区
- 下一篇： CLR无法从COM 上下文0x645e1