更新90万件数据的表,3个钟还没完,谁来优化一下我的PL/SQL

Question

目标是要更新主表KEI_ROOT的3个FIELD
3个项目都是根据从表里面的FIELD加以判断(比较复杂的),取值设定
再更新到主表的.
更新换10件还挺好,50件就要1个钟头,90万件还在跑,都一夜了,晕死大致代码如下

Answer 1

procedure root_match
is
  /*変数定義*/
  wk_pol         char(20);                    --証券番号
  wk_pol_eda     char(5);                     --証券番号枝番
  wk_kanpi_kbn   char(1);                     --幹非区分
  wk_root_rowid  rowid;                       --ROWID  
  ret_cd         number;                      --戻り値
  err_key_info   varchar2(1200);              --エラーキーPOL
  /*カウントｴﾘｱ*/
  cnt_root       number := 0;                 --root表処理件数
  cnt_jis_futai  number := 0;                 --地震付帯件数
  cnt_no_jis     number := 0;                 --付帯対象外件数
  cnt_kanping    number := 0;
  /* 定数の定義 */
  sry_id         constant varchar(50) := '<<契約データ整合処理>>';
  /*root表全件searchカーソル定義*/
  cursor cur_root is
         (select a.policy,a.pol_eda,
                 a.rowid as row_id
          from kei_root a
         where a.h_shumoku = '100');
  cur_root_data  cur_root%rowtype;  cursor cur_kyotu is
     (select b.kanpi
      into wk_kanpi_kbn
      from KEI_KYOTU_S b
     where b.policy        =  wk_pol
       and b.pol_eda       =  wk_pol_eda);
  cur_kyotu_data  cur_kyotu%rowtype;  /*例外定義*/
  jisin_shiki_get_err   exception;
  jisin_hantei_err      exception;begin
  /* 開始ログ出力 */
  logPrint(sry_id, '処理開始', null, 0);   /*カーソルオープン*/
  open cur_root;  loop
    fetch cur_root into cur_root_data;
    exit when cur_root%notfound;
    /*証券番号枝番、ROWIDを取得*/
    wk_pol := cur_root_data.policy;
    wk_pol_eda := cur_root_data.pol_eda;
    wk_root_rowid := cur_root_data.row_id;
    /*地震付帯有無判定*/
    ret_cd := jisin_hantei(wk_pol,wk_pol_eda,wk_root_rowid);
    if ret_cd = 0 then
      /*地震付帯対象件数＋１*/
      cnt_jis_futai := cnt_jis_futai+1;
      /*地震保険終期の設定*/
      ret_cd := jisin_shiki_set(wk_pol,wk_pol_eda,wk_root_rowid);
      if ret_cd <> 0 then
         err_key_info := '証券番号-枝番：'||trim(wk_pol)||'-'||trim(wk_pol_eda);
         raise jisin_shiki_get_err;
      end if;
    else
      if ret_cd = 9 then
         err_key_info := '証券番号-枝番：'||trim(wk_pol)||'-'||trim(wk_pol_eda);
         raise jisin_hantei_err;
      end if;
      /*地震付帯対象外件数＋１*/
      cnt_no_jis := cnt_no_jis + 1;
    end if;    /*幹非区分の設定*/
    open cur_kyotu;
    fetch cur_kyotu into cur_kyotu_data;
    if cur_kyotu%found then
      wk_kanpi_kbn := cur_kyotu_data.kanpi;
      if (wk_kanpi_kbn is null or wk_kanpi_kbn = ' ') then
      null;
      else
        update kei_root t
           set t.kanpi      =  wk_kanpi_kbn,
               t.timestamp  =  to_char(sysdate,'YYYYMMDDHH24MISS')
         where t.rowid = wk_root_rowid;
        cnt_kanping := cnt_kanping +1;
      end if;
    end if;
    close cur_kyotu;
    /*ルート処理対象件数＋１*/
    cnt_root := cnt_root +1;
if mod(cnt_root,10000) = 0 then
commit;
if mod(cnt_root,100000) = 0 then
logPrint('...', 'commit', null, cnt_root);
end if;
end if;
  end loop;
  /*カーソルクロース*/
  close cur_root;
  commit;

Answer 2

我是游标全件检索主表的的
取出主KEY再到从表里面去检索,再判断取值
完了再逐个更新主表
初写PLSQL,性能没怎么考虑,那个高人指点一下迷津

Answer 3

没看明白，但用这么多游标，一定慢拉，能不能直接用update去掉几个游标的工作

Answer 4

我实验用了
update 主表 t
   set t.kanpi = (select t.kanpi from  从表 t2
                   where t.key1 =  t2.key1
                     and t.key2 =  t2.key2
                     and t2.kanpi <> ' '
                     and t2.kanpi is not null) 更新47000多件,用了799秒,还没游标来的快.

Answer 5

我实验用了
update 主表 t
   set t.kanpi = (select t.kanpi from  从表 t2
                   where t.key1 =  t2.key1
                     and t.key2 =  t2.key2
                     and t2.kanpi <> ' '
                     and t2.kanpi is not null) 更新47000多件,用了799秒,还没游标来的快.－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
写的太有问题了吗，好累赘
set t.kanpi = (select t.kanpi from 
最终还是用ｔ表的字段kanpi 更新自己ｔ的kanpi 
好像不是一般的菜鸟写出来的

Answer 6

カーソルクロース  cursor close?  给日本干外包，辛苦了。

Answer 7

是否可以说一下涉及到的几张表的数据量?就是kei_root ,KEI_KYOTU_S 这两张表的数据量?

Answer 8

update 主表 t
   set t.kanpi = (select t.kanpi from  从表 t2
                   where t.key1 =  t2.key1
                     and t.key2 =  t2.key2
                     and t2.kanpi <> ' '
                     and t2.kanpi is not null) 
//上边写的那句话的意思是如果从表t2.kanpi是空值，那就就修改对应的主表t的记录的kanpi为空，那么对应的sql应该这么写好一些：
update 主表 t
   set kanpi = null
   where (key1,key2) in (select key1,key2 from 从表 t2 where nvl(kanpi,'')='');//但是根据搂主的写法，应该是想用从表的非空数据更新主表中对应的数据，如果是这样的话那楼主是真写错了，应该这么些才对：update 主表 t
   set t.kanpi = (select t2.kanpi from  从表 t2
                   where t.key1 =  t2.key1
                     and t.key2 =  t2.key2)
   where (key1,key2) in (select key1,key2 from 从表 t2 where nvl(kanpi,'')<>'');

Answer 9

数据量较大，建议先将要修改的值写入临时表，再关联。
前2天我遇到类似的问题。使用临时表只要几分钟，而不使用用了几个小时也没反应。
改写以下代码：
update 主表 t
   set t.kanpi = (select t2.kanpi from  从表 t2
                   where t.key1 =  t2.key1
                     and t.key2 =  t2.key2)
   where (key1,key2) in (select key1,key2 from 从表 t2 where nvl(kanpi,'')<>'');
改写如下：
insert into temp_table
       select key1,key2,kanpi
         from 从表
        where nvl(kanpi,'')<>'');update 主表 t
   set t.kanpi = (select t2.kanpi from  temp_table t2
                   where t.key1 =  t2.key1
                     and t.key2 =  t2.key2)   ;