当前访客身份:游客 [ 登录  | 注册加入尚学堂]
直播

我来了!

拥有积分:4024
尚学堂雄起!!威武。。。

博客分类

笔记中心

课题中心

提问中心

答题中心

解答题中心

数据批处理优化性能

我来了! 发表于 2年前 (2014-10-13 09:11:23)  |  评论(0)  |  阅读次数(469)| 0 人收藏此文章,   我要收藏   

2014-10-04 BaoXinjian

一、摘要


PLSQL_性能优化系列11_Oracle Bulk Collect

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

 

 

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

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

 

分享到:0
关注微信,跟着我们扩展技术视野。每天推送IT新技术文章,每周聚焦一门新技术。微信二维码如下:
微信公众账号:尚学堂(微信号:bjsxt-java)
北京总部地址:北京市海淀区西三旗桥东建材城西路85号神州科技园B座三层尚学堂 咨询电话:400-009-1906 010-56233821
Copyright 2007-2015 北京尚学堂科技有限公司 京ICP备13018289号-1 京公网安备11010802015183