刪除表中的數(shù)據(jù)的方法有delete,truncate,
它們都是刪除表中的數(shù)據(jù),而不能刪除表結(jié)構(gòu),delete 可以刪除整個(gè)表的數(shù)據(jù)也可以刪除表中某一條或N條滿足條件的數(shù)據(jù),而truncate只能刪除整個(gè)表的數(shù)據(jù),一般我們把delete 操作收作刪除表,而truncate操作叫作截?cái)啾?
truncate操作與delete操作對比
操作
回滾
高水線
空間
效率
Truncate
不能
下降
回收
快
delete
可以
不變
不回收
慢
下面分別用實(shí)例查看它們的不同
1.回滾
首先要明白兩點(diǎn)
1.在oracle 中數(shù)據(jù)刪除后還能回滾是因?yàn)樗言紨?shù)據(jù)放到了undo表空間,
2.DML語句使用undo表空間,DDL語句不使用undo,而delete是DML語句,truncate是DDL語句,別外DDL語句是隱式提交.
所以truncate操用不能回滾,而delete操作可以.
兩種操作對比(首先新建一個(gè)表,并插入數(shù)據(jù))
SQL> create table t
2 (
3 i number
4 );
Table created.
SQL> insert into t values(10);
SQL> commit;
Commit complete.
SQL> select * from t;
I
----------
10
Delete刪除,然后回滾
SQL> delete from t;
1 row deleted.
SQL> select * from t;
no rows selected
#刪除后回滾
SQL> rollback;
Rollback complete.
SQL> select * from t;
I
----------
10
Truncate截?cái)啾?然后回滾.
SQL> truncate table t;
Table truncated.
SQL> rollback;
Rollback complete.
SQL> select * from t;
no rows selected
可見delete刪除表還可以回滾,而truncate截?cái)啾砭筒荒芑貪L了.(前提是delete操作沒有提交)
2.高水線
所有的Oracle表都有一個(gè)容納數(shù)據(jù)的上限（很象一個(gè)水庫歷史最高的水位），我們把這個(gè)上限稱為“high water mark”或HWM。這個(gè)HWM是一個(gè)標(biāo)記(專門有一個(gè)數(shù)據(jù)塊用來記錄高水標(biāo)記等)，用來說明已經(jīng)有多少數(shù)據(jù)塊分配給這個(gè)表. HWM通常增長的幅度為一次5個(gè)數(shù)據(jù)塊.
delete語句不影響表所占用的數(shù)據(jù)塊, 高水線(high watermark)保持原位置不動(dòng)
truncate 語句缺省情況下空間釋放,除非使用reuse storage; truncate會(huì)將高水線復(fù)位
下面對兩種操作對比
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 列代表該表中曾經(jīng)使用過得
數(shù)據(jù)庫塊的數(shù)目，即水線。
注意：USER_TABLES.BLOCKS EMPTY_BLOCKS (20+3=23)比DBA_SEGMENTS.BLOCKS少一個(gè)
數(shù)據(jù)庫塊，這是因?yàn)橛幸粋€(gè)
數(shù)據(jù)庫塊被保留用作表頭。DBA_SEGMENTS.BLOCKS 表示分配給這個(gè)表的所有的
數(shù)據(jù)庫塊的數(shù)目。USER_TABLES.BLOCKS表示已經(jīng)使用過的
數(shù)據(jù)庫塊的數(shù)目(水線)。
Delete刪除表,
SQL> delete from t;
10000 rows deleted
SQL> commit;
Commit complete.
SQL> analyze table t estimate statistics;
Table analyzed.
SQL> select table_name,blocks,empty_blocks from user_tables where table_name=upper('t');
TABLE_NAME BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ----------------------------------------------------------------
T 20 3
Truncate截?cái)啾?div style="height:15px;">
SQL> truncate table t;
Table truncated.
SQL> analyze table t estimate statistics;
Table analyzed.
SQL> select table_name,blocks,empty_blocks from user_tables where table_name=upper('t');
TABLE_NAME BLOCKS EMPTY_BLOCKS
------------------------------ ---------- --------------------------------------------------------
T 0 7
可見,delete表,BLOCK(高水線)不變,而truncate表BLOCKS(高水線)變?yōu)?
現(xiàn)在我們也看到blocks+empty_blocks=7,也就是oracle分配區(qū)時(shí)默認(rèn)一次7+1(表頭)=8個(gè)blocks;
高水線的作用: HWM對
數(shù)據(jù)庫的操作有如下影響：
a) 全表掃描通常要讀出直到HWM標(biāo)記的所有的屬于該表
數(shù)據(jù)庫塊，即使該表中沒有任何數(shù)據(jù)。
b) 即使HWM以下有空閑的
數(shù)據(jù)庫塊，鍵入在插入數(shù)據(jù)時(shí)使用了append關(guān)鍵字，則在插入時(shí)使用HWM以上的數(shù)據(jù)塊，此時(shí)HWM會(huì)自動(dòng)增大。
因此高水線是oracle優(yōu)化時(shí)一個(gè)重要的參數(shù)
3.空間
既然高水線用來說明已經(jīng)有多少數(shù)據(jù)塊分配給這個(gè)表，那么高水線也可理解為表的空間占用。
即使delete將表中的數(shù)據(jù)全部刪除，HWM還是為原值，所以還有那么多的空間分配給這個(gè)表，即它的空間還沒有回收，
而truncate表后高水線變?yōu)?，那現(xiàn)在它就表示沒有分配空間，即它的空間被回收了。
4．效率
要想查看delete,truncate那個(gè)效率更高，先構(gòu)建一個(gè)大表，然后查看它們分別對些表刪除所需的時(shí)間。
有個(gè)相當(dāng)形象的比喻：領(lǐng)導(dǎo)給你兩本書讓你扔掉,delete就是你守在復(fù)印機(jī)前,把書一頁頁撕下來復(fù)印一份,再一頁頁扔到垃圾桶里,truncate就是直接把兩本書扔到垃圾桶里，那個(gè)快那個(gè)慢不言而喻。
先在表中插入100000條記錄，并打開時(shí)間
SQL> set timing on;
SQL> begin
2 for i in 1..100000 loop
3 insert into t values('10');
4 commit;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
Elapsed: 00:01:12.50
Delete刪除表
SQL> delete from t;
100000 rows deleted.
Elapsed: 00:00:20.09
Truncate 截?cái)啾?div style="height:15px;">
Rollback complete.
Table truncated.
