Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Girish.

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

Last updated: December 04, 2023 - 2:15 am UTC

Version: 19

Viewed 1000+ 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
    select col from 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;

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Syntax check

Abraham Olsen, December 01, 2023 - 10:12 am UTC

Sorry, but:

<b>  cursor cur is
    delete ...
</b>


Does this work?
Connor McDonald
December 04, 2023 - 2:15 am UTC

Thanks for catching the typo.

This has been corrected.