oracle三种删除的区别,三种批量删除PLSQL写法效率的比对
我們有一個重要的舊系統，最近夜維出現了一些問題，夜間執行5小時未完成，為了不影響業務，只能早上高峰期之前，DBA手工kill夜維進程。
這一個夜維程序采用了PLSQL寫的存儲過程，通過數據庫job定時啟動執行。存儲過程我很少使用，借著這次機會，補習了下，這個存儲過程中的邏輯比較簡單，依次刪除若干張業務表，每張表刪除的邏輯相同，為了便于說明，模擬了下刪除一張表的邏輯，示例如下，
TBL_CUSS表三個字段，第一個字段是NUMBER類型，第二個字段是VARCHAR2類型，第三個字段是DATE類型，
這個存儲過程接受一個參數，表示刪除幾天前的數據，刪除DELETE語句按照一個時間字段和這個參數比較得出符合條件的記錄集，同時使用rownum限制每次執行DELETE-COMMIT事務的條數，循環執行，直至出現ORA-1403無記錄的提示，退出此邏輯。這個存儲過程最優的地方，是使用了批量提交，不是執行一次DELETE刪除所有記錄COMMIT，如果這么執行，可能會占用大量的UNDO回滾段，進而可能出現回滾段空間不足的報錯，也可能出現ORA-1555的錯誤。畢竟UNDO中記錄的是SQL語句的逆向，對于DELETE語句，逆向就是INSERT，即會存儲刪除的整條記錄。
可能有朋友一眼就看出這個存儲過程的邏輯有一些問題，比如對于這種批量刪除，未使用游標，相當于每次要檢索tbl_cuss表符合insert_time < trunc(SYSDATE)-:1條件的記錄，可每次僅刪除其中的rownum限制的條數，如果使用游標，檢索只需要一次執行，不考慮是否有索引，執行語句次數的降低，可以帶來性能的一定提升。
針對此問題，寫了第二個存儲過程，
?
接受刪除天數的參數，使用了游標，執行一次SELECT，讀取出的則是符合insert_time < trunc(SYSDATE)-:1條件的所有結果集記錄的rowid信息，遍歷游標的時候使用BULK批量的方式，設置了一次性執行的條數限制MAX_ROW_SIZE，并且刪除語句是根據上面游標獲取的rowid為條件進行的DELETE，如果各位了解rowid，可以知道他代表了這條記錄的物理位置，通過換算可以得出這條記錄存儲于的文件、塊和行上，即可以快速定位這條記錄的物理位置，在RBO模式下，他的成本優先級是最優的，高于索引。
繼續寫了第三個存儲過程，
?
這和第二個存儲過程，基本一致，唯一不同就是第二個存儲過程中使用了for循環，第三個存儲過程則用forall循環。for循環會執行其中的每條SQL語句，forall則會將其中所有SQL批量發送SQL引擎執行。當然可能有其他的寫法，比如使用游標，但不使用BULK，按照rowid刪除，這種寫法執行SQL語句的次數和結果集數據量一致，效率可能還不如原始procedure。
從原理上說，使用BULK比單條語句執行，減少PLSQL和SQL引擎之間的切換頻率，也可以減少redo和undo的產生量。針對循環內執行的DELETE，適合于使用集合，放入forall。
delete和insert都可以從forall上面得到巨大的性能提升。但是對于update來說opcode沒有相關操作，提升應該不會那么明顯。
接下來我們會對這三個存儲過程進行一些比對實驗，通過一些數據，說明各自的適用場景，首先創建測試數據集，制造了1300萬測試數據，
?
每天50萬數據，一共26天，
第一個存儲過程名稱為clear_without_fetch。
第二個存儲過程名稱為clear_all_fetch。
第三個存儲過程名稱為clear_fetch。
實驗有以下幾類，(以下執行基本采用第二次執行的結果避免第一次刷緩存)
(1) 一次性刪除1萬條記錄，insert_time不是索引，刪除兩天的數據(即100萬)，
clear_without_fetch用時01:16.31
clear_all_fetch用時00:40.50
clear_fetch用時00:21.73
clear_fetch勝出，clear_without_fetch最慢，說明TABLE ACCESS FULL下的SQL語句，一次性刪除1萬條記錄，使用游標和BULK效率要高些，使用forall比for效率要高些。
(2) 一次性刪除5萬條記錄，insert_time不是索引，刪除兩天的數據(即100萬)，
clear_without_fetch用時00:26.98
clear_all_fetch用時00:39.80
clear_fetch用時00:22.24
clear_fetch勝出，但一次性刪除5萬條記錄，TABLE ACCESS FULL下的SQL語句由于執行次數減小為原來的5倍，效率上有提升，clear_all_fetch基本一致，是因為無論什么方式，其執行SQL語句的次數，和結果集一致，即100萬次SQL。
(3) 一次性刪除100萬條記錄，insert_time不是索引，刪除兩天的數據(即100萬)，
clear_without_fetch用時00:21.92
clear_all_fetch用時01:22.00
clear_fetch用時00:25.95
clear_without_fetch勝出，TABLE ACCESS FULL下的SQL語句執行一次，clear_fetch雖然仍是批量一次發送SQL，性能上的優勢不很明顯。
(4) 一次性刪除1萬條記錄，insert_time是索引，刪除兩天的數據(即100萬)，
clear_without_fetch用時00:31.01
clear_all_fetch用時01:09.02
clear_fetch用時00:37.39
clear_without_fetch勝出，索引掃描執行效率提升，相比TABLE ACCESS FULL要明顯一些。
(5) 一次性刪除5萬條記錄，insert_time是索引，刪除兩天的數據(即100萬)，
clear_without_fetch用時00:35.35
clear_all_fetch用時01:03.26
clear_fetch用時00:37.40
clear_without_fetch勝出，clear_all_fetch和clear_fetch基本保持和1萬一致。
(6) 一次性刪除100萬條記錄，insert_time是索引，刪除兩天的數據(即100萬)，
clear_without_fetch用時00:23.33
clear_all_fetch用時01:27.80
clear_fetch用時00:33.68
clear_without_fetch勝出，相比1萬和5萬，效率提升一些，我理解主要是SQL執行次數從100次(1萬)->20次(5萬)->1次(100萬)。
上面的實驗中，數據接近的未必是絕對，和環境因素(例如機器配置、數據質量等)可能有關，因此大體方向上可以參考，不同次的實驗可能會略有不同，但方向應該比較接近，畢竟原理擺著。
如下是上面六個實驗，三個存儲過程SQL，各自執行的10046的trace，從中可以看出一些端倪，
clear_without_fetch存儲過程各場景trace，
(1) 一次性刪除1萬條記錄，insert_time不是索引，刪除兩天的數據(即100萬)，
可以看出由于使用了綁定變量，解析一次，由于循環邏輯的問題，執行了100+1次。
(2) 一次性刪除5萬條記錄，insert_time不是索引，刪除兩天的數據(即100萬)，
(3) 一次性刪除100萬條記錄，insert_time不是索引，刪除兩天的數據(即100萬)，
從elapsed以及query可以比較(1)-(3)。
(4) 一次性刪除1萬條記錄，insert_time是索引，刪除兩天的數據(即100萬)，
(5) 一次性刪除5萬條記錄，insert_time是索引，刪除兩天的數據(即100萬)，
(6) 一次性刪除100萬條記錄，insert_time是索引，刪除兩天的數據(即100萬)，
由于需要維護索引，相比TABLE ACCESS FULL，會有些消耗。
clear_all_fetch存儲過程各場景trace，
(1) 一次性刪除1萬條記錄，insert_time不是索引，刪除兩天的數據(即100萬)，
(2) 一次性刪除5萬條記錄，insert_time不是索引，刪除兩天的數據(即100萬)，
(3) 一次性刪除100萬條記錄，insert_time不是索引，刪除兩天的數據(即100萬)，
無論(1)、(2)、(3)，DELETE語句均執行了100萬次，唯一的區別就是SELECT語句和執行的次數。
(4) 一次性刪除1萬條記錄，insert_time是索引，刪除兩天的數據(即100萬)，
(5) 一次性刪除5萬條記錄，insert_time是索引，刪除兩天的數據(即100萬)，
(6) 一次性刪除100萬條記錄，insert_time是索引，刪除兩天的數據(即100萬)，
有索引和無索引相比，有一些需要維護索引的消耗。
clear_fetch存儲過程各場景trace，
(SELECT和clear_all_fetch存儲過程相近，此處忽略)
(1) 一次性刪除1萬條記錄，insert_time不是索引，刪除兩天的數據(即100萬)，
(2) 一次性刪除5萬條記錄，insert_time不是索引，刪除兩天的數據(即100萬)，
(3) 一次性刪除100萬條記錄，insert_time不是索引，刪除兩天的數據(即100萬)，
(4) 一次性刪除1萬條記錄，insert_time是索引，刪除兩天的數據(即100萬)，
(5) 一次性刪除5萬條記錄，insert_time是索引，刪除兩天的數據(即100萬)，
(6) 一次性刪除100萬條記錄，insert_time是索引，刪除兩天的數據(即100萬)，
可以看見clear_fetch和clear_all_fetch唯一區別就是DELETE語句執行次數，clear_fetch中執行次數和循環次數一樣，說明是批量發送的，單條DELETE相同，但執行次數的不同，影響了資源消耗和執行時間。
從實驗中可以得出的結論，
(1) SQL使用TABLE ACCESS FULL的執行計劃，若SQL執行次數較多時，則BULK+forall的方式，效率較高；若SQL執行次數較少時，很可能使用TABLE ACCESS FULL的執行計劃的SQL，效率和BULK+forall接近，甚至有更優的可能。
(2) SQL使用INDEX RANGE SCAN的執行計劃，效率會比BULK+forall略高，若SQL執行次數較少時，使用INDEX RANGE SCAN的執行計劃的SQL，效率較高；SQL執行次數對于BULK+forall的方式基本一致。
(3) 無論是否用索引，BULK+forall的方式均優于BULK+for。可以使用索引，則用游標和不用游標，效率比較接近，從實驗上看，不用游標反而可能略高一些，這和使用游標需要一些解析類的消耗可能有關，但游標可以帶來便捷性，比如方便控制結果集，可以更靈活地編輯邏輯，既然效率比較接近，若時間均是可接受范圍內，可以根據實際來考慮，選擇什么方式。無論什么方式，大表數據的批量刪除，這是首要原則。
總結
以上是生活随笔為你收集整理的oracle三种删除的区别,三种批量删除PLSQL写法效率的比对的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 比亚迪 7000 车汽车运输船首制船在广
- 下一篇： 触控 + 物理按键：小米智能家庭面板 2