Skip to Main Content


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


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


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

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

  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;
  open cur;
    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
  end loop;
  close cur;

More to Explore


The Oracle documentation contains a complete SQL reference.