truncate和delete的區別及DDL,DML,DCL,TCL 基礎概念

truncate和delete的區別及DDL,DML,DCL,TCL 基礎概念


先瞭解 DDL,DML,DCL,TCL 基礎概念

對於SQL語言，有兩個組成部分：

DML（data manipulation language）：它們是SELECT、UPDATE、INSERT、DELETE，就象它的名字一樣，這4條命令是用來對資料庫裡的資料進行操作的語言。

DDL（data definition language）：DDL比DML要多，主要的命令有CREATE、ALTER、DROP等，DDL主要是用在定義或改變表（TABLE）的結構，資料類型，表之間的連結和約束等初始化工作上，他們大多在建立表時使用。 回答者：zhongsojun | 三級| 2009-9-1 19:30

          

 SQL語言共分為四大類：資料查詢語言DQL，資料操縱語言DML，

資料定義語言 (Data Definition Language)DDL，資料控制語言DCL。

1 資料查詢語言DQL

資料查詢語言DQL基本結構是由SELECT子句，FROM子句，WHERE

子句組成的查詢塊：

SELECT <欄位名表>

FROM <表或視圖名>

WHERE <查詢條件>

2 資料操縱語言

資料操縱語言DML主要有三種形式：

1) 插入：INSERT

2) 更新：UPDATE

3) 刪除：DELETE

 

3 資料定義語言 (Data Definition Language)DDL

資料定義語言 (Data Definition Language)DDL用來建立資料庫中的各種對象-----表、視圖、

索引、同義字、聚簇等如：

CREATE TABLE/VIEW/INDEX/SYN/CLUSTER

 

4 資料控制語言DCL

資料控制語言DCL用來授予或回收訪問資料庫的某種特權，並控制

資料庫操縱事務發生的時間及效果，對資料庫實行監視等。如：

1) GRANT：授權。

2) ROLLBACK [WORK] TO [SAVEPOINT]：回退到某一點。

復原---ROLLBACK

復原命令使資料庫狀態回到上次最後提交的狀態。其格式為：

SQL>ROLLBACK;

3) COMMIT [WORK]：提交。 www.2cto.com

在資料庫的插入、刪除和修改操作時，只有當事務在提交到資料

庫時才算完成。在事務提交前，只有操作資料庫的這個人才能有權看

到所做的事情，別人只有在最後提交完成後才可以看到。

提交資料有三種類型：顯式提交、隱式提交及自動認可。下面分

別說明這三種類型。

(1) 顯式提交

用COMMIT命令直接完成的提交為顯式提交。其格式為：

SQL>COMMIT；

(2) 隱式提交

用SQL命令間接完成的提交為隱式提交。這些命令是：

ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP，

EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME。

(3) 自動認可

若把AUTOCOMMIT設定為ON，則在插入、修改、刪除語句執行後，

系統將自動進行提交，這就是自動認可。其格式為：

SQL>SET AUTOCOMMIT ON；




&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&





執行個體對比Oracle中truncate和delete的區別
刪除表中的資料的方法有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秒.
本文出自 “追求” 部落格，請務必保留此出處http://chenxy.blog.51cto.com/729966/168459