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