深度分析DROP,TRUNCATE与DELETE的区别【我的数据库之路系列】

这次特意从数据库块的层次深度分析一下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语句，它直接对数据进行操作，但并非立刻就删除数据，而是先做上标志，之后在重组数据块的时候清除，或者满足某些条件（如数据块填满，高水位线重置等）的时候被新数据覆盖掉。

原文地址：http://hi.baidu.com/bjn_wuming/item/6e2e59e1422b01b42e140b63