海量数据处理_批量更新

问题：
   经常需要在有限的投产停机窗口完成大量数据设置，比如根据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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/18922393/viewspace-696622/