plsql更新百万条记录表的性能检查

Question

下面的块尝试使用表 t2 中找到的数据更新大表 t1。当我更新具有 500 条记录的注释代码但需要 30 分钟才能更新超过 1000 条记录时，这似乎很好。我尝试了批量收集更新和注释代码索引，没有太多时间差异。

DECLARE
  
  
   lv_row_count            NUMBER(9) := 0;
   lv_total_count          NUMBER(9) := 0;  
   lv_commit_cnt           SIMPLE_INTEGER:=0;
   
BEGIN
         FOR rec in 
         (SELECT 
           a.t1_id,
           a.t1_orig_code,
           t2_orig_code,
           a.t1_comment_code,     
           t2_code,
           a.t1_restrict_update_ind,
           t2_restrict_update_ind,
           a.t1_data_origin,
           t2_data_origin,
           a.t1_purge_ind,
           t2_purge_ind,
           a.t1_created_date,
           a.rowid
     FROM t1 a     
     JOIN t2 ON t2_code = a.t1_comment_code
    WHERE  a.t1_comment_code in ('A','B','C','C1','D3')
      AND ( a.t1_orig_code != t2_orig_code  OR  a.t1_restrict_update_ind !=t2_restrict_update_ind 
           OR  a.t1_data_origin != t2_data_origin OR  a.t1_purge_ind != t2_purge_ind) 
  )
          LOOP
            lv_total_count := lv_total_count + 1;           
          
                UPDATE t1 
                   SET t1_ORIG_CODE= rec.t2_orig_code  
                       t1_RESTRICT_UPDATE_IND = 'Y',,
                       t1_DATA_ORIGIN = rec.t2_data_origin,
                       t1_PURGE_IND =rec.t2_purge_ind                  
                 WHERE t1.rowid =rec.rowid ;
                 
                   lv_commit_cnt:=lv_commit_cnt+1;
                    IF MOD(lv_commit_cnt,lv_limit)=0 THEN
                       lv_commit_cnt:=0;
                       COMMIT;
                     END IF;
                                          dbms_output.put_line('a.t1_pidm -'||rec.t1_pidm ||
          'a.t1_orig_code -'||rec.t1_orig_code ||'Updated');         


           
           END LOOP;
           COMMIT;
           dbms_output.put_line('Total_count- '||lv_total_count);
           -- dbms_output.put_line('No record');
        
END;

感谢对此的投入。

Answer 1

毫不奇怪，这需要很长时间。逐行处理是很慢的。

那么合并怎么样？

merge into t1
  using t2
  on (t1.t1_comment_code = t2.t2_code)
when matched then update set
  t1.t1_orig_code = t2.t2_orig_code,
  t1.t1_restrict_update_ind = 'Y',
  t1.t1_data_origin = t2.t2_data_origin,
  t1.t1_purge_ind = t2.t2_purge_ind
where t1.t1_comment_code in ('A', 'B', 'C', 'C1', 'D3');

就这样；没有 PL/SQL，没有循环，循环中间没有提交……什么都没有。只需合并即可。