淺談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跳出了外層的循環。