Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Girish.

Asked: January 18, 2023 - 11:38 am UTC

Last updated: January 18, 2023 - 1:43 pm UTC

Version: 19

Viewed 100+ times

You Asked

Hi,

We have a requirement to delete rows in chunks of say 100 from a large table and commit, then proceed for next 100 delete.
Without looping delete written is as below

delete from large_table
 where code=888
   and id in (select id from small_table
              OFFSET 100 ROWS FETCH NEXT 100 ROWS ONLY);


Will this be right approach ? If not how to write a single delete statement

Thanks,
Girish

and Chris said...

If you want to delete in batches of 100, you'll have to use some kind of loop.

Generally, it's fastest to delete all the data in one go, e.g.:

delete large_table
 where code=888
   and id in (select id from small_table);


If you're deleting LOTS of rows, this can be very slow; for faster alternatives see https://blogs.oracle.com/sql/post/how-to-delete-millions-of-rows-fast-with-sql

If you must delete in batches of N, then look into bulk processing, which would look something like:

declare
  cursor cur is
    delete large_table
    where  code=888
    and    id in ( select id from small_table );
  type cur_arr is table of cur%rowtype
    index by pls_integer;
  arr cur_arr;
begin
  open cur;
  loop
    fetch cur 
    bulk  collect 
    into  arr
    limit 100
    
    exit when arr.count = 0;
    
    forall i in 1 .. arr.count 
      delete large_table
      where  col = arr(i).col
    commit;
    
  end loop;
  close cur;
end;

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.