我做的小實(shí)驗(yàn)，LZ參考看看
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as ljb
SQL> drop table test;
Table dropped
SQL> create table test tablespace cdma as select * from dba_objects;
Table created
查看一下該表大小，為8388608個(gè)字節(jié)
SQL> select segment_name,bytes,tablespace_name from user_segments where segment_name='TEST';
SEGMENT_NAME BYTES TABLESPACE_NAME
------------------------------- -----------------------------------------------------
TEST 8388608 CDMA
SQL> delete from test;
62654 rows deleted
SQL> commit;
Commit complete
確實(shí)，發(fā)現(xiàn)刪除提交后，表大小仍然為8388608個(gè)字節(jié)個(gè)字節(jié)，并沒(méi)釋放，此時(shí)如果查詢(xún)?cè)摫碛涗?，雖然有記錄，但是和剛才沒(méi)記錄查詢(xún)一樣慢，這點(diǎn)在前面已經(jīng)說(shuō)過(guò)了。
SQL> select segment_name,bytes,tablespace_name from user_segments where segment_name='TEST';
SEGMENT_NAME BYTES TABLESPACE_NAME
------------------------------- -----------------------------------------------------
TEST 8388608 CDMA
?????????????????????????
那如果再次插入呢？是否空間大小是8388608的兩倍呢？
SQL> insert into test select * from dba_objects;
62654 rows inserted
SQL> commit;
Commit complete
驚奇的發(fā)現(xiàn)，大小仍然一樣
SQL> select segment_name,bytes,tablespace_name from user_segments where segment_name='TEST';
SEGMENT_NAME BYTES TABLESPACE_NAME
------------------------------- -----------------------------------------------------
TEST 8388608 CDMA
下面用另外一個(gè)方式來(lái)查看，show_space是顯示表詳細(xì)情況的過(guò)程
SQL> set serverout on
SQL> exec show_space('TEST','auto');

Total Blocks............................1024
Total Bytes.............................8388608
Unused Blocks...........................125
Unused Bytes............................1024000
Last Used Ext FileId....................77
Last Used Ext BlockId...................820489
Last Used Block.........................3

PL/SQL procedure successfully completed

SQL> delete from test;
62654 rows deleted
SQL> commit;
刪除再次插入，仍然大小一樣，而且所有的細(xì)項(xiàng)都保持不變！
Commit complete
SQL> exec show_space('TEST','auto');

Total Blocks............................1024
Total Bytes.............................8388608
Unused Blocks...........................125
Unused Bytes............................1024000
Last Used Ext FileId....................77
Last Used Ext BlockId...................820489
Last Used Block.........................3
PL/SQL procedure successfully completed
總結(jié)：delete是不能立即釋放空間，但是卻是可以被重用，也就是某個(gè)應(yīng)用經(jīng)常刪除再經(jīng)常插入，并不會(huì)有太多的空塊！對(duì)于頻繁插入和更新的表，運(yùn)行慢，不該懷疑是因?yàn)槔锩嬗刑嗫諌K。