Oracle刪除大量資料的例子

一、引言
從來沒有想過，刪除表中的資料都會成為問題。但是，當表中的資料量非常大時，刪除資料會成為一個不小的問題。

這裡只描述解決過程，不涉及SQL、預存程序的寫法。方法很簡單，高手繞行。

二、情境
運行了一年多的生產庫，每小時大約五萬條資料，今天發現有一個星期程式出了問題，整整一個星期的資料是重複的。需要把重複的資料選出來刪除。

三、解決過程
（一）

重複資料刪除資料的SQL很簡單，用ROWID來排除就可以了，最開始想到的方法是使用一個SQL來解決問題。很快寫出來，在測試庫上驗證成功。

開啟SQL Plus，執行刪除SQL。結果執行了一天多，都沒有執行完，而且由於時間太長，SQL Plus與伺服器的串連已經斷開了。

不知是不是連線逾時導致SQL沒有執行完，還是執行完了串連斷開事務沒有提交而復原了。專門去查了一下如何控制串連發獃時間，ORACLE有個參數可以控制，IDLE_TIME，但查到的資料說，長查詢時，串連不會斷開（事實證明好像不是這樣，有點兒奇怪，難道執行時間很長的刪除與長查詢不一樣？？？）修改IDEL_TIME會對系統運行有一定的影響，我決定還是不改它，想辦法最佳化刪除的方法。

（二）

接下來想到的方法是，先把重複的資料記錄的ROWID找到，儲存到一個暫存資料表中，然後根據這個表使用預存程序來刪除，每一萬條提交一次資料，防止過多的UNDO資料，提高效率，同時避免連線逾時，交易回復。

結果也是執行了一天多，沒有結果，連線逾時。

（三）

由天預存程序執行完成，才會顯示過程中dbms_output.put_line輸出的資訊，專門建了個表記錄預存程序執行過程中的調試資訊。發現將生重複資料的ROWID找到、插入暫存資料表的時間特別長。

把尋找重複資料的範圍進行縮減，調整為依次對每個小時的進行尋找、使用大量操作（buld collect into，forall）的方法來刪除。在生產庫上進行了測試，大約5分鐘執行完畢，效果不錯。

再寫一個預存程序，依次執行按小時刪除的預存程序。

（四）

結果還是沒有執行成功，連線逾時。看來這個連線逾時還真是個問題，可能是由於連線逾時導致操作失敗。但我每個小預存程序中都有提交操作，那也應該能完成部分資料的刪除操作呀？事實上是沒有任何資料被刪除。這個問題我沒有想通。

最後解決的辦法就是，使用一個簡單粗暴的方法：

exec delete_by_hour('2016101400');

exec delete_by_hour('2016101401');

exec delete_by_hour('2016101402');

……

寫了大量這樣的調用過程，複製到剪貼簿中，然後粘到SQL Plus中，這些命令乖乖地一個接一個地執行，最後順利地刪除了所有資料，一共用了13個小時。