linux 批量更新,批量更新总结

最近遇到一个这么一个业务:每晚根据另一个系统上报的数据(表 t_taemployee ,100W左右数据),更新正式系统里面对应名单(表tatable, 2000W左右数据)的状态等信息,基于商业原因,这里的表名及字段名都已做处理,分别采用别名代替.

实现方式:定义一个procedure,采用Job来定时调用procedure执行,其procedure代码(已处理过)如下

create or replace procedure upload_state as

curyear  number;

curmonth number;

tatable varchar(100) := 't_test1_';

fetable varchar(100) := 't_test2_';

cout     number := 0;

rowindex number := 0;

exc_time exception;

exc_ta exception;

exc_fe exception;

insert_err varchar(200) := 'insert into t_synerror2(message) values(:message)';

type v_cursor is ref cursor;

y_cursor  v_cursor;

f_cursor v_cursor;

query_y   varchar2(1000);

query_f  varchar2(1000);

type y_rec is record(

state  number,

sje   number,

row_id varchar2(20));

type f_rec is record(

state  number,

row_id varchar2(20));

type y_array_type is table of y_rec;

type f_array_type is table of f_rec;

y_array  y_array_type;

f_array f_array_type;

begin

select count(1)

into cout

from user_tables

where table_name = upper('t_synerror2');

if cout = 0 then

execute immediate 'create table t_synerror2(

notedate date default sysdate,

message varchar(100))';

end if;

select extract(year from(sysdate - 1)), extract(month from(sysdate - 1))

into curyear, curmonth

from dual;

if curmonth = 2 or curmonth = 8 then

raise exc_time;

end if;

tatable := tatable || curyear || curmonth;

select count(1)

into cout

from user_tables

where table_name = upper(tatable);

if cout = 0 then

raise exc_ta;

end if;

fetable := fetable || curyear || curmonth;

select count(1)

into cout

from user_tables

where table_name = upper(fetable);

if cout = 0 then

raise exc_fe;

end if;

--bluck collect

--

query_y := 'select ad.state, ad.sje, y.rowid row_id

from st_goup sj, t_taemployee ad, ' ||

tatable || ' y

where sj.js_id = ad.sid

and sj.state = 1

and sj.q_id = y.sid';

open y_cursor for query_y;

fetch y_cursor bulk collect

into y_array;

close y_cursor;

for r in y_array.first .. y_array.last loop

execute immediate 'update ' || tatable ||

' y set y.state = :state, y.sje  = :sje where rowid=:rowd'

using y_array(r).state, y_array(r).sje, y_array(r).row_id;

rowindex := rowindex + 1;

if rowindex = 1000 then

rowindex := 0;

commit;

end if;

end loop;

commit;

/*

execute immediate '

update ' || tatable || ' y

set y.state = (select ad.state

from st_goup sj, t_taemployee ad

where sj.js_id = ad.sid

and sj.state = 1

and q_id = y.sid),

y.sje  = (select ad.sje

from st_goup sj, t_taemployee ad

where sj.js_id = ad.sid

and sj.state = 1

and q_id = y.sid)

where exists (select sj.q_id

from st_goup sj, t_taemployee ad

where sj.js_id = ad.sid

and sj.state = 1

and q_id = y.sid)';

commit;

*/

--bluck collect

--

query_f := 'select fe.state, ff.rowid

from st_goup    sj,

t_femployee      fe,

' || fetable || ' ff

where sj.js_id = fe.sid

and sj.state = 1

and q_id = ff.sid';

open f_cursor for query_f;

fetch f_cursor bulk collect

into f_array;

close f_cursor;

rowindex := 0;

for r in f_array.first .. f_array.last loop

execute immediate 'update ' || fetable || ' f

set f.state = :state where rowid = :row_id'

using f_array(r).state, f_array(r).row_id;

rowindex := rowindex + 1;

if rowindex = 1000 then

rowindex := 0;

commit;

end if;

end loop;

commit;

/*

execute immediate '

update ' || fetable || ' f

set f.state = (select fe.state

from st_goup sj, t_femployee fe

where sj.js_id = fe.sid

and sj.state = 1

and q_id = f.sid)

where exists (select sj.q_id

from st_goup sj, t_femployee fe

where sj.js_id = fe.sid

and sj.state = 1

and q_id = f.sid)';

commit;

*/

exception

when exc_time then

execute immediate insert_err

using '当前系统月份为“' || curmonth || '”，...';

commit;

when exc_ta then

execute immediate insert_err

using '当前系统年月(' || curyear || '，' || curmonth || ')...“' || tatable || '”还未生成';

commit;

when exc_fe then

execute immediate insert_err

using '当前系统年月(' || curyear || '，' || curmonth || ')...“' || fetable || '”还未生成';

commit;

end upload_state;

/

以上是使用bulk collect into优化后的效果,该脚本通过job调用,一分钟左右执行完成.

而优化前,没有用到bulk collect into ,而是直接update(注释掉的脚本),执行了近5小时,最后不得不手动停止Job执行.

这里仅仅提供给大家一个批量更新的参考,不做过多说明.

补充：根据上月状态，初始化本月的状态，涉及两张千万级的表，采用以下脚本，使用了 bulk collect into limit 获取方式和 forall循环相结合，根据 rowid 更新记录，对系统正常使用不会造成太大的影响，执行了10分半钟。

declare

cursor md_20113 is

select /*+parallel(y1,10) parallel(y3,10)+*/

y3.rowid

from pub_md_20111 y1, pub_md_20113 y3

where y1.student_id = y3.student_id

and y1.state = y3.state

and y1.state = 3;

type md_rec is table of varchar2(20);

row_id md_rec;

begin

open md_20113;

loop

fetch md_20113 bulk collect

into row_id limit 10000;

forall i in row_id.first .. row_id.last execute immediate

'update pub_md_20113 set state = 0 where rowid=:rid'

using row_id(i)

;

commit;

exit when md_20113%notfound;

end loop;

close md_20113;

end;

/