ORACLE 高效的批量处理

两个大表join，b表5千万，c表7千万，统计出佣金更新a表。

原语句：

UPDATE TMP_GX_UNDW3820 A
     SET (A.佣金) = (SELECT SUM(B.COMMISSION)
                       FROM PREM_INFO C, LCS_COMMISSION_TRAIL B
                      WHERE C.PK_SERIAL# = B.PK_SERIAL#
                        AND A.保单号 = C.POLNO);

全表扫描，一天都跑不出结果。

增加两个索引：

create index t_prem on aud_lucs0.prem_info(POLNO,pk_serial#); 

create index t_lcs on aud_lucs0.LCS_COMMISSION_TRAIL(pk_serial#,commission);

 

修改一下select 部分，大概1小时不到可以出结果。

SELECT/*+index(c, t_prem) index(b, t_lcs ) use_hash(b c)*/SUM(B.COMMISSION)
                       FROM PREM_INFO C, LCS_COMMISSION_TRAIL B
                      WHERE C.PK_SERIAL# = B.PK_SERIAL#
                        group by  C.POLNO;

 

 

采用bulk collect 方式，22秒完成。效率不是一般的高~~~ 

set serveroutput on

declare

  type t_polno is table of aud_lucs0.TMP_GX_UNDW3820.保单号%type index by binary_integer;  

 

  v_p t_polno;

begin

  select 保单号 bulk collect into v_p from aud_lucs0.TMP_GX_UNDW3820 ;

  forall i in v_p.first .. v_p.last 

    UPDATE TMP_GX_UNDW3820 A

       SET (A.佣金) =

           (SELECT /*+index(b t_lcs)*/

             SUM(B.COMMISSION)

              FROM PREM_INFO C, LCS_COMMISSION_TRAIL B

             WHERE C.PK_SERIAL# = B.PK_SERIAL#

               and c.polno =a.保单号)

     where a.保单号 = v_p(i);

end;

/