DELETE大批次資料的效能最佳化

DELETE大批次資料的效能最佳化[@more@]

大批次資料的效能最佳化

問題的提出：

一個表有上千萬的資料，欲從該表中刪除部分資料；

線上用的生產庫，不能影響生產；

該表有四個索引，刪除的sql語句用到了索引；

正常業務不能停止－不能disable約束，也不能臨時不用索引；

問題的解決：

這個問題中應該重點考慮的問題是這個系統是生產系統，不能離線，所以只能是根據情況考慮一些線上的刪除方式。

1、 如果是定期清楚歷史資料的話,可以考慮用partition的方式，採用分割槽方式是其優點之一，能夠使用其易於管理的一個方面，在這裡是不是應該考慮分割槽時候是採用我們需要刪除的資料為分割槽的原則。

2、 增大回滾段，透過設定使用這個回滾段，來提高系統的刪除操作的速度，但是對於Oracle9i以後的系統來說，由於使用了自動管理回滾段的情況，如何進行回滾段的設定。

3、 增大redo log線上檔案的大小，以便減少checkpoint的執行頻率，增大buffer來提高刪除的速度。這個主要目的是減少系統checkpoint的次數，儘量少減少磁碟IO的次數，能夠在資料快取上執行的儘量在記憶體中實現。

4、 使用rowid來刪除資料。

1．根據使用者所提需求過濾需要刪除的源資料，create 臨時表 as select rowid rid from 需要刪除的表 where 刪除條件。

2．使用pl/sql block來刪除資料，這樣能保證及時遞交，防止lock過多的行導致系統負載增加。這裡需要注意commit的頻率，一般為更新100條記錄commit一次。

create table temp_del表名_040803 as select rowid rid from 要刪除的表 where ...... declare execrow number; begin execrow:=1; for i in 1..需要更新的記錄數/100(取整) loop delete 要刪除的表 where rowid in(select rid from temp_del表名_040803 where rownum<100); delete from temp_del表名_040803 where rownum<100; commit; end loop; end; /

記得再建一張臨時表儲存要刪除的內容已備回滾。這樣根據rowid來刪除的話效率會比較高。

5、 在Oracle 9i之後，可以考慮使用bulk delete的新功能。（這個沒有高畫質楚是什麼東西）

6、 如果在不是生產庫的時候，如果索引沒有在刪除時使用的情況下，（如果在刪除的語句中使用了索引得話，這樣能夠提高刪除的速度）可以將索引刪除，等執行完刪除語句的時候，可以考慮重建索引，因為刪除索引上的資料的時候也是需要時間和系統消耗的，這時候需要看看你所要刪除的資料量的多少，如果資料量相對整個表比較大的時候，可以考慮使用全表掃描，這樣應該是更快的方式，如果比率比較小的時候，使用索引找到rowid來刪除應該是比較好的選擇。

7、 有人提出這樣的觀點：

相關問題，俺們也經常遇到，共享經驗如下： 1.表分割槽異常重要 2.索引過多很是麻煩 3.相關約束更是頭痛（4M資料如果有N個子表對其參照，乖乖..) 4.表空間如過是dictionary管理，必須選擇分段批次提交(俺們的資料庫都被鎖死了） 5.會滾、排序,大redo,多個arch執行緒，必須養成習慣，任何大資料量批次操作，都不可少。 6.臨時表（在臨時表空間上）用於查詢果然較快。 7.引用偶像的話“Delete就是慢,就是慢,就是慢.”，沒辦法。 最後還是想說，象這樣的工作只有考慮應用的特徵，才會找到比較好的方法（俺覺得）

問題：表空間如過是dictionary管理，必須選擇分段批次提交(俺們的資料庫都被鎖死了），為什麼是這樣的？