PLSQL_性能優化系列11_Oracle Bulk Collect批處理

2014-10-04 Created By BaoXinjian

一、摘要

PL/SQL程式中運作SQL語句是存在開銷的，因為SQL語句是要送出給SQL引擎處理。

這種在PL/SQL引擎和SQL引擎之間的控制轉移叫做上下文卻換，每次卻換時，都有額外的開銷。

但是，FORALL和BULK COLLECT可以讓PL/SQL引擎把多個上下文卻換壓縮成一個，這使得在PL/SQL中的要處理多行記錄的SQL語句執行的花費時間驟降。

1. FORALL與BULK COLLECT的使用方法:

(1). 使用FORALL比FOR效率高，因為前者隻切換一次上下文，而後者将是在循環次數一樣多個上下文間切換。

(2). 使用BLUK COLLECT一次取出一個資料集合，比用遊标條取資料效率高，尤其是在網絡不大好的情況下。但BLUK COLLECT需要大量記憶體。

2. 未使用批處理時，系統需頻繁切換上下文

3. 使用批處理後，系統隻需切換上下文一次

二、批處理 - Bulk Collect

采用BULK COLLECT可以将查詢結果一次性地加載到Collections中，而不是通過Cursor一條一條地處理。

可以在Select Into ，Fetch Into，Returning Into語句使用BULK COLLECT。

注意在使用BULK COLLECT時，所有的INTO變量都必須是Collections。

1. Select Into 中使用Bulk Collect

DECLARE
   TYPE table_forall IS TABLE OF test_forall%ROWTYPE;
   v_table   table_forall;
BEGIN
   SELECT   mub.user_id, mub.user_name
     BULK   COLLECT
     INTO   v_table
     FROM   mag_user_basic mub
    WHERE   mub.user_id BETWEEN 10000 AND 10100;

   FORALL idx IN 1 .. v_table.COUNT
      INSERT INTO test_forall
        VALUES   v_table (idx);

   --VALUES(v_table(idx).user_id,v_table(idx).user_name);Error
   --在PL/SQL中，BULK In-BIND與RECORD，%ROWTYPE是不能在一塊使用的，
   --也就是說，BULK In-BIND隻能與簡單類型的數組一塊使用
   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK;
END;      

2. Fetch Into 中使用Bulk Collect

DECLARE
   TYPE table_forall IS TABLE OF test_forall%ROWTYPE;
   v_table   table_forall;

   CURSOR c1
   IS
      SELECT   mub.user_id, mub.user_name
        FROM   mag_user_basic mub
       WHERE   mub.user_id BETWEEN 10000 AND 10100;
BEGIN
   OPEN c1;

   --在fetch into中使用bulk collect
   FETCH c1 BULK COLLECT INTO   v_table;

   FORALL idx IN 1 .. v_table.COUNT
      INSERT INTO test_forall
        VALUES   v_table (idx);

   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK;
END;      

3. 在Returning Into中使用Bulk Collect

DECLARE
   TYPE IdList IS TABLE OF test_forall.User_Id%TYPE;
   enums   IdList;
   TYPE NameList IS TABLE OF test_forall.user_name%TYPE;
   names   NameList;
BEGIN
   DELETE FROM   test_forall2
         WHERE   user_id = 10100
     RETURNING   user_id, user_name BULK COLLECT INTO   enums, names;

   DBMS_OUTPUT.put_line ('Deleted ' || SQL%ROWCOUNT || ' rows:');

   FOR i IN enums.FIRST .. enums.LAST
   LOOP
      DBMS_OUTPUT.put_line ('User #' || enums (i) || ': ' || names (i));
   END LOOP;

   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK;
END;      

三、批處理 - ForAll

FORALL告訴PL/SQL引擎要先把一個或多個集合的所有成員都綁定到SQL語句中，然後再把語句發送給SQL引擎。

1. 批量更新中，将For改成Forall

(1). 使用For語句進行Update

DECLARE
    TYPE NumList IS VARRAY(20) OF NUMBER;
    depts NumList := NumList(10, 30, 70, ...);
    -- department numbers
BEGIN
  ...
  FOR i IN depts.FIRST..depts.LAST
  LOOP
    ...
    --UPDATE statement is sent to the SQL engine
    -- with each iteration of the FOR loop!
    UPDATE emp SET sal = sal * 1.10 WHERE deptno = depts(i);
  END LOOP:
END;      

(2). 使用Forall語句的批處理進行Update

--UPDATE statement is sent to the SQL engine just once, with the entire nested table
FORALL i IN depts.FIRST..depts.LAST
UPDATE   emp
   SET   sal = sal * 1.10
 WHERE   deptno = depts (i);