解決Oracle性能優化中的問題

在Oracle性能優化時，用exists替代in,用表鏈接替代exists，關於前者，一般效果比較明顯，exists效率明顯比in高，但是如果要想表連接的效率比exists高，必須在from子句中，將記錄多的表放在前面，記錄少的表放後面。

　　關於select... bulk collect into ... limit ...或fetch... bulk collect into ... limit ...句型：

　　在使用如上句型時，通常我們都會用for或forall循環進行insert/update/delete操作。

　　for/forall循環方法有好幾種，如

　　第1種：

　　

for tp in tmp.FIRST.. tmp.LAST loop .... end loop;

　　第2種：

　　

for tp in 1 .. tmp.COUNT loop .... end loop;

　　第3種：

　　

for tp in indecs of tmp loop .... end loop;

　　上面的第1種方法有一個致使的弱點，就是在select... bulk collect into ... limit ...或fetch... bulk collect into ... limit ...沒有取到數據時，如果沒有exit，則第一種方法會報錯：ORA-06502: PL/SQL: numeric or value error。因為tmp.FIRST訪問不存在，為空值。必須對錯誤進行錯誤處理。而在嵌套的循環中，內層的循環是不能用exit的，所有必然遇到這種錯誤。

　　第2種方法不會出現這種問題，第3種方法沒有試過。

　　借鑒網上的做法，給出一種使用綁定變量的批量刪除數據的存儲過程：

　　

PROCEDURE RemoveBat2DjaRecords(參數)

AS type RowIdArray is table of rowid index by binary_integer;

 rowIds RowIdArray;

 BEGIN loop select rowid BULK COLLECT into rowIds　from 表名 

where 查詢條件 and rownum <= 1000; 

exit when SQL%NOTFOUND; 

forall k in 1 .. rowIds.COUNT

 delete from 表名 where rowid = rowIds(k); 

commit; 

end loop;

 EXCEPTION when OTHERS then rollback; 

END RemoveBat2DjaRecords;

　　上面的1000條是一個可以設定的數，根據你的服務器性能可以擴大或縮小。

用exit跳出循環，通常情況下，exit只跳出當前層的循環，與其它程序設計語言的break語句類似。在嵌套的循環中，如果要直接從內層循環跳出外面多層的循環，可使用'EXIT 標簽 When'形式的語句，舉例如下：

　　

SQL> BEGIN 

2  <> 

3 FOR v_outerloopcounter IN 1..2 LOOP 

4   <> 

5 FOR v_innerloopcounter IN 1..4 LOOP

 6 DBMS_OUTPUT.PUT_LINE('Outer Loop counter is ' 

7 || v_outerloopcounter || 

8 ' Inner Loop counter is ' || v_innerloopcounter);

 9 EXIT WHEN v_innerloopcounter = 3; 

10 END LOOP innerloop; 

11 END LOOP outerloop; 

12 END; 

13 / 

Outer Loop counter is 1 Inner Loop counter is 1

 Outer Loop counter is 1 Inner Loop counter is 2 

Outer Loop counter is 1 Inner Loop counter is 3

 Outer Loop counter is 2 Inner Loop counter is 1 

Outer Loop counter is 2 Inner Loop counter is 2 

Outer Loop counter is 2 Inner Loop counter is 3 

PL/SQL procedure successfully completed.

　　從上面可以看出，普通情況下，exit只跳出當前層的循環。

SQL> BEGIN 

2 <> 

3 FOR v_outerloopcounter IN 1..2 LOOP

 4 <> 

5 FOR v_innerloopcounter IN 1..4 LOOP 

6 DBMS_OUTPUT.PUT_LINE('Outer Loop counter is ' 

7 || v_outerloopcounter || 

8 ' Inner Loop counter is ' || v_innerloopcounter); 

9 EXIT outerloop WHEN v_innerloopcounter = 3; 

10 END LOOP innerloop; 

11 END LOOP outerloop; 

12 END; 

13 /

 Outer Loop counter is 1 Inner Loop counter is 1

 Outer Loop counter is 1 Inner Loop counter is 2 

Outer Loop counter is 1 Inner Loop counter is 3 

PL/SQL procedure successfully completed.

　　從上面可以看出，exit跳出了外層的循環