海量資料處理_批量更新

問題：
   經常需要在有限的投產停機視窗完成大量資料設定，比如根據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/，如需轉載，請註明出處，否則將追究法律責任。