Delete is taking considerable amount of time

Question

I have written a plsql block to delete some records for a bunch of tables. so to identify the records to be deleted I have created a cursor on top that query.

        declare
        type t_guid_invoice is  ref cursor;
        c_invoice   t_guid_invoice;
    
        begin
        open c_invoice for 
        select * from a,b where a.col=b.col ;--(quite a complex join,renders 200k records)
        loop fetch c_invoice into col1,col2,col3;
           exit when c_invoice%NOTFOUND;
    
     begin
                       DELETE
          FROM tab2
          WHERE cola= col1;
          
            if SQL%rowcount > 0 then
            dbms_output.put_line ( 'INFO: tab2 for ' || col1|| '/' || col2|| ' removed.');
                    else
            dbms_output.put_line ( 'WARN: No tab2 for ' || col1|| '/' || col2|| ' found!');
                
            end if;
    eXception
        when others then
        dbms_output.put_line ( 'ERR: Problems while deleting tab2 for ' || col1|| '/' || col2  );
        dbms_output.put_line ( SQLERRM );
        end;
....

end loop;

This continues to loop through about 26 tables, there are some tables which are as big as 60 million records. deletion is based on primary key in each table. All triggers are disabled before deletion process. if I try to delete 10k records, it loops through 10k times, deleting multiple rows in each table but its taking as long as 30 minutes. There is no commit after each block, since I have to cater to simulation mode too. Any suggestions to speed up the process? Thanks!

Answer 1

For sure, if you loop 10k times, all those DBMS_OUTPUT.PUT_LINE calls will slow things down (even if you aren't doing anything "smart") (and I wonder whether buffer is large enough). If you want to log what's going on, create a log table and an autonomous transaction procedure which will insert that info (and commit).

Apart from that, are tables properly indexed? E.g. that would be cola column in tab2 table (in code you posted). Did you collect statistics on tables and indexes? Probably wouldn't harm if you do that for the whole schema.

Did you check explain plan?

Do you know what takes the most time? Is it a ref cursor query (so it has to be optimized), or is it deleting itself?

Can't you avoid loop entirely? Row-by-row processing is slow. For example, instead of using ref cursor, create a table out of it, index it, and use it as

create table c_invoice as
  select * from a join b on a.col = b.col;

create index i1inv_col1 on c_invoice (col1);

delete from tab2 t
where exists (select null 
              from c_invoice c
              where c.col1 = t.cola
             );