CODEWITHSUNDEEP
oracle-databaseperformanceplsqlsql-updatequery-optimization
arsha
arsha

Reputation: 67

Performance check on plsql update to million record table

The below block tries to update a large table t1 with data found in table t2.It seems fine when i update for comment code that has 500 records but takes 30 minutes to update more than 1000 records. I tried the bulk collect update and index on comment code there is not much of time difference.

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;

Appreciate inputs on this .

Upvotes: 0

Views: 88

Answers (1)

Littlefoot
Littlefoot

Reputation: 143163

No surprise it takes ages; row-by-row processing is slow-by-slow.

How about merge, instead?

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');

Just like that; no PL/SQL, no loop, no commit in the middle of the loop ... nothing. Just merge.

Upvotes: 1

Related Questions