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!

score 0 · Accepted Answer · answered Sep 07 '21 at 09:58

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

I tried creating a table approach. delete FROM tab_a a WHERE exists (SELECT 1 FROM tab_b b where a.CONTRACT=b.CONTRACT ); tab_a contains 202652355 Rows tab_b contains 300k records. contract column in both the table are indexed. they are non unique. Its still taking a lot of time. any suggestions. — Rashmi Suresh, Sep 15 '21 at 08:19
200 million rows is quite a lot; I'd expect it to take time. — Littlefoot, Sep 15 '21 at 08:30

Marmite Bomber · Answer 2 · 2021-09-07T12:15:33.193

You typically never want to delete a large number of rows from a table in a loop.

You want to use one DELETE statement with an appropriate WHERE condition.

Additionaly while processing a large number of rows you typically do not want to use an index.

So your first step would be to check the rows that would not be deleted (your warnings)

You get the keys with the following query and you may log them

select a.col from a,b where a.col=b.col
minus
select cola from tab2;

In the second step you delete all rows with one statement.

delete 
from tab2
where cola in (select a.col from a,b where a.col=b.col);

In problems check the execution plan, you expect TABLE ACCESS FULL (INDEX FAST FULL SCAN is fine as well) on all sources combined with a HASH JOIN.

Delete is taking considerable amount of time

2 Answers2