刪除表中的資料的方法有delete,truncate,

它們都是刪除表中的資料,而不能刪除表結構,delete 可以刪除整個表的資料也可以刪除表中某一條或N條滿足條件的資料,而truncate只能刪除整個表的資料,一般我們把delete 操作收作刪除表,而truncate操作叫作截斷表.

truncate操作與delete操作對比

操作	回滾	高水線	空間	效率
Truncate	不能	下降	回收	快
delete	可以	不變	不回收	慢

下面分別用例項檢視它們的不同

1.回滾

首先要明白兩點

1.在oracle 中資料刪除後還能回滾是因為它把原始資料放到了undo表空間,

2.DML語句使用undo表空間,DDL語句不使用undo,而delete是DML語句,truncate是DDL語句,別外DDL語句是隱式提交.

所以truncate操用不能回滾,而delete操作可以.

兩種操作對比(首先新建一個表,並插入資料)

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截斷表,然後回滾.

SQL> truncate table t;

Table truncated.

SQL> rollback;

Rollback complete.

SQL> select * from t;

no rows selected

可見delete刪除表還可以回滾,而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表示已經使用過的塊的數目(水線)。

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截斷表

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(高水線)變為0

現在我們也看到blocks+empty_blocks=7,也就是oracle分配區時預設一次7+1(表頭)=8個blocks;

高水線的作用: HWM對的操作有如下影響：

a) 全表掃描通常要讀出直到HWM標記的所有的屬於該表塊，即使該表中沒有任何資料。

b) 即使HWM以下有空閒的塊，鍵入在插入資料時使用了append關鍵字，則在插入時使用HWM以上的資料塊，此時HWM會自動增大。

因此高水線是oracle最佳化時一個重要的引數

3.空間

既然高水線用來說明已經有多少資料塊分配給這個表，那麼高水線也可理解為表的空間佔用。

即使delete將表中的資料全部刪除，HWM還是為原值，所以還有那麼多的空間分配給這個表，即它的空間還沒有回收，

而truncate表後高水線變為0，那現在它就表示沒有分配空間，即它的空間被回收了。

4．效率

要想檢視delete,truncate那個效率更高，先構建一個大表，然後檢視它們分別對些表刪除所需的時間。

有個相當形象的比喻：領導給你兩本書讓你扔掉,delete就是你守在影印機前,把書一頁頁撕下來影印一份,再一頁頁扔到垃圾桶裡,truncate就是直接把兩本書扔到垃圾桶裡，那個快那個慢不言而喻。

先在表中插入100000條記錄，並開啟時間

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 截斷表

#先把表回滾

SQL> rollback;

Rollback complete.

Elapsed: 00:00:17.36

SQL> select count(*) from t;

COUNT(*)

-------------------

100000

Elapsed: 00:00:00.01

SQL> truncate table t;

Table truncated.

Elapsed: 00:00:00.20

可見刪除同一個大小的表，delete用了20.09秒，而truncate只用了0.2秒.

Oracle中truncate和delete的區別

1.回滾

2.高水線

3.空間

4．效率

相關文章