Oracle資料庫刪除表中重複記錄的常見方法

 方法一： 
    DELETE FORM tb_channel a WHERE a.rowid IN
                (SELECT MAX(b.rowid) FROM tb_channle b 
    WHERE a.policyno=b.policyno AND a.classcode=b.classcode);

——這一辦法在資料記錄超過10萬時一般都會變得很慢。

 

方法二：

--建立暫存資料表，--清空原表，--插回原表，如下例： 
    CREATE TABLE temp_emp as (SELECT DISTINCT * FROM employee) ; 
    truncate table employee; 
    INSERT INTO employee SELECT * FROM temp_emp;

——這一辦法適用於較大的表的情況。因為是塊操作，對應於大表效率會好很多。

 

方法三：
--建立新表，--去重複放入，--刪除原表，如下例: 
    SELECT DISTINCT * INTO new_table FROM old_table 
    ORDER BY primary_key
    DROP TABLE old_table 
    EXEC sp_rename new_table,old_table;

——這一辦法適用於較大的表的情況。因為是塊操作，對應於大表效率會好很多。

 

truncate table 與 delete tabel的區別

一、
1.delete產生rollback，如果刪除大資料量的錶速度會很慢，同時會佔用很多的rollback segments,truncate是DDL操作,不產生rollback,速度快一些。
　　
2.不從tablespace中騰出空間,需要
　　
　　ALTER TABLESPACE AAA COALESCE; 才有空間
　　
3.truncate調整high water mark 而delete不.truncate之後，TABLE的HWM退回到 INITIAL和NEXT的位置（預設），delete 則不可以。
　　
4.truncate 只能對table,delete 可以是table,view,synonym
　　
5.TRUNCATE TABLE 的對象必須是本模式下的，或者有drop any table的許可權 而DELETE 則是對象必須是本模式下的，或被授予 DELETE ON SCHEMA.TABLE 或DELETE ANY TABLE的許可權

二、
truncate是DDL語言.
delete是DML語言

DDL語言是自動提交的.命令完成就不可回滾.
truncate的速度也比delete要快得多. 所以，delete 後要commit work，而truncate就不需要啦。

三、
truncate 會把 highwatermark 迴歸至 0 ... 當下一次再插入新資料時就會快一些啦。
所以一般都是在 temp table 上使用的，不過要注意就是 truncate 不能在 pl/sql 上使用，要用 dynamic SQL 才可以。

 

四、
當你不再需要該表時， 用 drop;
當你仍要保留該表，但要刪除所有記錄時， 用truncate;
當你要刪除部分記錄時（always with a WHERE clause), 用 delete.

 

五、

TRUNCATE TABLE Index也會刪掉不是指drop index