批量更新linux数据,海量数据处理_批量更新

问题：

经常需要在有限的投产停机窗口完成大量数据设置，比如根据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;

/