ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 海量数据处理_批量更新

海量数据处理_批量更新

原创 Linux操作系统 作者:redhouser 时间:2011-05-30 13:27:18 0 删除 编辑

问题:
   经常需要在有限的投产停机窗口完成大量数据设置,比如根据A表的情况更新B表的某列。有如下几种方法:
1,PL/SQL中的循环查找处理
2,使用可更新视图,并行更新
第一种方法最直观,适用于只更新少量数据;
第二种方法需要确保更新列属于“可更新视图”,效率较高,但事务所需回滚段空间较大,更新过程也无法监控。

实践中,我采用PL/SQL中批量更新实现,所需回滚段空间很小,更新过程也可监控。

主要操作步骤:
1,通过并行连接操作,将需要更新的记录插入临时表
2,使用FORALL批量更行,批量提交
3,批量更新时使用dbms_application_info.set_session_longops更新会话状态。在批量更新时,就可以使用
如下语句查看进度:
SELECT * FROM v$session_longops
 WHERE pname = 'update users_chnl.sec_id';

要点:
1,第一步操作中使用需要更新的目标表rowid进行排序,便于后续更新时对同一数据块同时处理,可以加快速度;
2,使用con_row_limit控制事务大小,10000能满足大部分需求,该值太大、太小都会影响速度;
3,如果预期异常较多,需要将dbms_output方式更改为插入处理日志表方式,以免屏幕输出影响处理速度。

样本:
1,创建临时表:
drop table tmp_users_chnl_rd;
create table tmp_users_chnl_rd nologging
as
SELECT /*+ parallel(cus 8) parallel(u 8) parallel(uc 8) */
         uc.rowid rd,(case when u.message_num is not null then 40 else 8 end) sec_id
          FROM customer cus, users u, users_chnl uc
         WHERE cus.cust_id = u.cust_id
           AND u.user_id = uc.user_id
           AND uc.sec_id is null
order by uc.rowid;


2,批量更新:
set serveroutput on;
DECLARE
  --异常
  array_dml_errors EXCEPTION;
  PRAGMA EXCEPTION_INIT(array_dml_errors, -24381);

  --集合
  TYPE rowidtab IS TABLE OF tmp_users_chnl_rd.rd%TYPE INDEX BY PLS_INTEGER;
  l_rowidtab rowidtab;
  TYPE secidtab IS TABLE OF tmp_users_chnl_rd.sec_id%TYPE INDEX BY PLS_INTEGER;
  l_secidtab secidtab;

  --游标
  TYPE cursor_ref IS REF CURSOR;
  cur cursor_ref;

  --count
  con_row_limit CONSTANT PLS_INTEGER := 10000;
  l_batch INT;
  l_count INT;

  --v$session_longops
  l_rindex BINARY_INTEGER;
  l_slno   BINARY_INTEGER;

  --error
  l_errors NUMBER;
  l_index  NUMBER;

BEGIN
  dbms_output.enable(NULL);
  dbms_output.put_line('---Begin update users_chnl.sec_id');

  l_rindex := dbms_application_info.set_session_longops_nohint;
  OPEN cur FOR 'select rd,sec_id from tmp_users_chnl_rd ';
  l_count := 0;
  l_batch := 0;

  LOOP
    --分批
    FETCH cur BULK COLLECT
      INTO l_rowidtab,l_secidtab LIMIT con_row_limit;
    EXIT WHEN l_rowidtab.COUNT = 0;
    l_count := l_count + l_rowidtab.COUNT;
 
    --更新
    BEGIN
      FORALL i IN l_rowidtab.FIRST .. l_rowidtab.LAST SAVE EXCEPTIONS
        UPDATE users_chnl SET sec_id = l_secidtab(i) WHERE ROWID = l_rowidtab(i);
      COMMIT;
    EXCEPTION
      --在批量处理时报错,输出每个错误
      WHEN array_dml_errors THEN
        COMMIT;
        l_errors := SQL%BULK_EXCEPTIONS.COUNT;
        dbms_output.put_line('Update users_chnl failed: ' || l_errors);
        FOR j IN 1 .. l_errors LOOP
          l_index := SQL%BULK_EXCEPTIONS(j).ERROR_INDEX;
          dbms_output.put_line('++ rowid,secid:' || l_rowidtab(l_index) || ','
                        || l_secidtab(l_index) || ','
                               || SQLERRM(-sql%BULK_EXCEPTIONS(j).ERROR_CODE));
        END LOOP;
      WHEN OTHERS THEN
        COMMIT;
        dbms_output.put_line(dbms_utility.format_error_stack);
    END;
 
    --更新过程
    l_batch := l_batch + 1;
    dbms_application_info.set_session_longops(rindex  => l_rindex,
                                              slno    => l_slno,
                                              op_name => 'update users_chnl.sec_id',
                                              sofar   => l_batch *
                                                         con_row_limit);
    IF con_row_limit >= 1000 THEN
      dbms_output.put_line('Processed:' || l_count);
    END IF;
  END LOOP;

  CLOSE cur;
  dbms_output.put_line('---OK! Processed:' || lpad(l_count, 20, ' '));
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line(dbms_utility.format_error_stack);
    CLOSE cur;
END;
/

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/18922393/viewspace-696622/,如需转载,请注明出处,否则将追究法律责任。

上一篇: DUAL是真实的表吗
请登录后发表评论 登录
全部评论

注册时间:2011-05-26

  • 博文量
    211
  • 访问量
    810031