Oracle 中truncate與delete的區別

標籤：return   database   清除   作用   lan   清空   comm   invoice   statement   

truncate是oracle中專屬的關鍵字吧！它的作用是清空一個表格，在刪除資料方面，其與delete有一些區別，以便自己記住：

 

1、在功能上，truncate是清空一個表的內容，它相當於delete from table_name。
2、delete是dml操作，truncate是ddl操作；因此，用delete刪除整個表的資料時，會產生大量的roolback，佔用很多的rollback segments， 而truncate不會。
3、在記憶體中，用delete刪除資料，資料表空間中其被刪除資料的表佔用的空間還在，便於以後的使用，另外它是“假相”的刪除，相當於windows中用delete刪除資料是把資料放到資源回收筒中，還可以恢複，當然如果這個時候重新啟動系統（OS或者RDBMS），它也就不能恢複了！
而用truncate清除資料，記憶體中資料表空間中其被刪除資料的表佔用的空間會被立即釋放，相當於windows中用shift+delete刪除資料，不能夠恢複！
4、truncate 調整high water mark 而delete不；truncate之後，TABLE的HWM退回到 INITIAL和NEXT的位置（預設）delete 則不可以。
5、truncate 只能對TABLE，delete 可以是table,view,synonym。
6、TRUNCATE TABLE 的對象必須是本模式下的，或者有drop any table的許可權 而 DELETE 則是對象必須是本模式下的，或被授予 DELETE ON SCHEMA.TABLE 或DELETE ANY TABLE的許可權。
7、在外層中，truncate或者delete後，其佔用的空間都將釋放。
8、truncate和delete只刪除資料，而drop則刪除整個表（結構和資料）。 

 

delete 用法

Oracle Delete Statements 
Version 10.2 
  
Basic Delete Statements 
1.Delete All Rows:  DELETE <table_name>
                               or
                               DELETE FROM <table_name>; 
                               CREATE TABLE t  AS
                               SELECT *
                                FROM all_tables;

                                SELECT COUNT(*)
                                FROM t;

DELETE FROM t;

COMMIT;

SELECT COUNT(*)
FROM t;

2.Delete Selective Rows

 DELETE FROM <table_name>
WHERE <condition>; 
CREATE TABLE t AS
SELECT *
FROM all_tables;

SELECT COUNT(*)
FROM t;

DELETE FROM t
WHERE table_name LIKE ‘%MAP‘;

COMMIT;

SELECT COUNT(*)
FROM t;

3.Delete From A SELECT Statement

DELETE FROM (<SELECT Statement>); 
CREATE TABLE t AS
SELECT *
FROM all_tables;

SELECT COUNT(*)
FROM t;

DELETE FROM (
  SELECT * FROM t WHERE table_name LIKE ‘%MAP‘);

SELECT COUNT(*)
FROM t;

4.Delete With Returning Clause

 DELETE FROM (<SELECT Statement>); 
CREATE TABLE t AS
SELECT *
FROM all_tables;

set serveroutput on

DECLARE
 r  urowid; 
BEGIN
  DELETE FROM t
  WHERE rownum = 1
  RETURNING rowid INTO r;

  dbms_output.put_line(r);
END;
/ 

5.Delete Restricted To A Partition

DELETE FROM <table_name>
PARTITION <partition_name>; 
DELETE FROM sales PARTITION (q1_2001_invoices); 
Delete From A Remote Database DELETE FROM <table_name>@<database_link> 
DELETE FROM [email protected]_db; 
 

 在刪除大資料量時(一個表中大部分資料時),

方法：
1、先將不需要刪除的資料複製到一個暫存資料表中
2、trunc table 表
3、將不需要刪除的資料複製回來。

delete只迴圈了一次,(去查並刪)
遊標迴圈了二次,(先查出來,再迴圈一次刪)

Oracle 中truncate與delete的區別