如何在plsql程序中处理批量数据

如何在plsql程序处理批量数据.Author: Wenxing.zhongDate: 2008-05-29在ebao的应用程序中，有很多的业务需要通过pl/sql程序来处理，每天晚上可能要运行多个job来处理当天，当周或者当月的数据；这些后台批处理pl/sql程序在业务数据量比较少的场合基本能够在一个晚上的时间内跑完，但是随着客户业务数据量的不断增长，部分pl/sql程序的执行时间不断的增长，以至于一个晚上跑不完影响业务系统白天的正常运行，引起客户不断的抱怨，给项目组带来比较大的压力；笔者在income和taiping项目的优化中，经历了其中的许多案例，现把优化过程中的一些体会写下来，供各个项目组参考；在和开发人员的交流中，开发人员不断向我抱怨，数据量太多了，我们没有办法提高程序的处理速度，我们必须一条一条数据来进行处理，因为里面有很多的业务逻辑处理，真的没有办法吗，不，有，只是我们没有发现，下面我罗列出一些针对批量处理的方法，希望能够抛砖引玉，给各个开发人员开阔一些思路；批量数据处理的一些方法：1，使用oracle批量处理的特性，如forall,bulk collect ；2，使用临时表来储存常用的一些数据，避免对大表的多次访问；3，使用多个job来并行处理；4，优化sql,提高sql的执行效率；一：使用oracle批量处理特性：(1)bulk collect 的使用：在ebao的程序中经常会有这样的程序：先声明一个游标CURSOR c_policy_fee ISSELECT *FROM t_policy_fee where xxxxx=xxxx;然后For cur_rec in c_policy_fee loop业务处理End loop;在游标获取的数据量较大的场合考虑用如下的方法：TYPE id_type IS TABLE OF t_policy_fee.id%TYPE;TYPE description_type IS TABLE OF t_policy_fee.description%TYPE;t_id id_type;t_description description_type;BEGINSELECT id, descriptionBULK COLLECT INTO t_id, t_description FROM t_policy_fee;For I in id.first st loop业务处理End loop;我们可以做一个测试，来看看两者速度的差异：SQL> create table tst_policy_fee as select * from t_policy_fee where rownum<1000000;SQL>Table createdExecuted in 15.625 secondsSQL>SQL> DECLARE2 TYPE id_type IS TABLE OF tst_policy_fee.fee_id%TYPE;3 TYPE description_type IS TABLE OFtst_policy_fee.FINISH_TIME%TYPE;45 t_id id_type := id_type();6 t_description description_type := description_type();78 CURSOR c_data IS9 SELECT fee_id,FINISH_TIME10 FROM tst_policy_fee;11 BEGIN12 FOR cur_rec IN c_data LOOP13 t_id.extend;14 t_description.extend;1516 t_id(t_st) := cur_rec.fee_id;17 t_description(t_st) := cur_rec.FINISH_TIME;18 END LOOP;19 END;20 /SQL>PL/SQL procedure successfully completedEx ecuted in 7.343 secondsSQL>SQL> DECLARE2 TYPE id_type IS TABLE OF tst_policy_fee.fee_id%TYPE;3 TYPE description_type IS TABLE OFtst_policy_fee.FINISH_TIME%TYPE;45 t_id id_type;6 t_description description_type;7 BEGIN8 SELECT fee_id,FINISH_TIME9 BULK COLLECT INTO t_id, t_description FROM tst_policy_fee;10 END;11 /PL/SQL procedure successfully completeExecuted in 2.282 seconds可以看出速度有提高，也许有人说这样并没有显著的提高，不过，在循环处理中，从cache中取数据和从游标中取数据还是有区分的；Bulk collect主要是为了提高批量查询的速度，当然如果数据量不大，性能就体现不出来了；(2)使用forall特性：Forall主要提高批量insert,update,delete操作；对批量插入的操作：在ebao的程序中，可能有如下的一些需求：For cur_red in cursor1 loop业务处理Insert into tablexx values(xx,xx,xx);End loop;在数据量少的场合，觉得没有问题，但是数据量一旦到5万，10万。

性能就会有影响了，其实我们这里可以用到批量插入的方法：如：先声明一个typetype tab_interim_trans is table of t_tps_gl_interim_trans%rowtype index by binary_integer;tab_interim_trans_rec tab_interim_trans;For cur_red in cursor1 loop业务处理给tab_interim_trans_rec(i) 填充值；End loop;Forall I IN 1.. tab_interim_trans_rec.COUNTINSERT INTO t_tps_gl_interim_trans VALUEStab_interim_trans_rec(i);如以下的测试：常规100万数据的插入速度测试：SQL> DECLARE2 CURSOR c_data IS3 SELECT fee_id,FINISH_TIME4 FROM tst_policy_fee;5 BEGIN6 FOR cur_rec IN c_data LOOP7 insert into tst_forall(fee_id,finish_time)values(cur_rec.fee_id,cur_rec.finish_time);8 END LOOP;9 commit;10 END;11 /SQL>PL/SQL procedure successfully completedExecuted in 53.062 seconds使用oracle 特性100万数据的插入速度测试：SQL> DECLARE2 i pls_integer :=0;3 type tab_tst_forall is table of tst_forall%rowtype index by binary_integer;4 tab_tst_forall_rec tab_tst_forall;5 CURSOR c_data IS6 SELECT fee_id,FINISH_TIME7 FROM tst_policy_fee;8 BEGIN9 FOR cur_rec IN c_data LOOP10 i :=i +1;11 tab_tst_forall_rec(i).fee_id:=cur_rec.fee_id;12 tab_tst_forall_rec(i).FINISH_TIME:=cur_rec.FINISH_TIME;13 END LOOP;14 forall i in 1..tab_tst_forall_rec.count15 insert into tst_forall values tab_tst_forall_rec(i);16 commit;17 END;18 /PL/SQL procedure successfully completedExecuted in 17.344 seconds我们测试用的表的字段较少，在实际生产上，性能能够提高的更多。

对于批量更新的操作：在我们的程序中，存在如下的需求：For cur_red in cursor1 loop业务处理Update table1 set x=x+1 where …….End loop;在和开发人员的交流中，开发人员向我抱怨，我们这个没有办法优化，因为我需要用到循环中的值来做更新；其实不然，我们可以用一些type对象或者临时表来把用来更新的值先存储起来，如下：For cur_red in cursor1 loop业务处理Insert into temp values(1,xx,xx);或者：给temp_tbl充值；End loop;Select * bulk collect into temp_tbl from temp;Forall I in 1..temp_tbl.countUpdate tablexx set col1=temp_tbl(i).col1 wherecol2=temp_tbl(i).col2常规的批量更新100万数据：SQL>SQL> DECLARE2 m_fee_id tst_policy_fee.fee_id%TYPE;3 CURSOR c_data IS4 SELECT fee_id5 FROM tst_policy_fee;6 BEGIN7 FOR cur_rec IN c_data LOOP8 m_fee_id:= cur_rec.fee_id;9 update tst_policy_fee set finish_time=sysdate wherefee_id=m_fee_id;10 END LOOP;11 END;12 /PL/SQL procedure successfully completedExecuted in 379.266 seconds使用oracle特性的批量更新100万数据：SQL> DECLARE2 TYPE fee_id_type IS TABLE OF tst_policy_fee.fee_id%TYPE;3 fee_id_tbl fee_id_type;4 BEGIN5 SELECT fee_id6 BULK COLLECT INTO fee_id_tbl FROM tst_policy_fee;7 forall i in 1..fee_id_tbl.count8 update tst_policy_fee set finish_time=sysdate wherefee_id=fee_id_tbl(i);9 END;10 /SQL>SQL>SQL>PL/SQL procedure successfully completedExecuted in 204.735 seconds二：使用临时表来避免对大表的多次访问在我们的程序中，经常看到这种现象，先从表table1获取到一些数据，然后循环去访问大表，但其实在这个循环中，只用到了大表中的部分数据，如只是查询t_policy_fee表中10月份的数据；在这种情况下，我们可以考虑在循环外先把这部分数据存放到一个临时表中（注意：只需要把用到的字段存放到临时表即可）：如：在循环中，有如下的sql:select tpf.fee_id,tpf.policy_type,tpf.fee_type from t_policy_fee tpfwhere tpf.finish_time >= to_date(‘20080701’)and tpf.finish_time < to_date(‘20080701’)+ 1and tpf.posted = 'N'and tpf.cred_id is nulland tpf.fee_status in (6, 1);我们可以修改成：在循环还，用一个临时表把数据先插入到一个临时表中：insert into t_tpf_policy_fee select tpf.fee_id,tpf.policy_type,tpf.fee_type fromt_policy_fee tpfwhere tpf.finish_time >= i_start_dateand tpf.finish_time < i_end_date + 1and tpf.posted = 'N'and tpf.cred_id is nulland tpf.fee_status in (6, 1);然后在循还中访问临时表t_tpf_policy_fee；三：使用多个job并发运行业务处理我们可能会碰到这样的一个场景，从一个大表中抽取出100万数据，然后需要逐条对这100万数据进行处理，而且这个循还操作没有办法避免，那我们其实可以考虑把这100万数据分为4部分，然后用四个job并发运行，这样相当于每个job只需要运行25万数据，可以提高3/4的时间，但是这样操作必须有一个限制，就是db服务器的cpu必须要由多个，能够启动的job数取决于服务器的cpu个数，一般job数可以设置为（num of cpu -1 ）;四：提高程序中sql的执行效率在我们很多pl/sql程序中，可能处理的数据量不多，但是批处理还是运行比较长的时间，很有可能是我们程序中存在一些性能不佳的sql，我们可以把这些sql单独拿出来运行并请dba进行相关的优化；。