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);

 

Thanks and Regards

参考：http://www.jb51.net/article/35424.htm

参考：http://log-cd.iteye.com/blog/411122