I am trying to delete from one of the table and it is expected to delete millions of rows everytime.
The SQL , I am using is as below.
delete from xyz where time_key
in (select time_key from dss_dims_time where period_year = 2000) AND rownum < 10001
I am trying to delete 10000 rows at a time , in order to avoid blowing up the rollback segment.
I am thinking of writing a PL/SQL procedure in which a cursor having all rowids matching for the condition and deleting one by one in a loop and checking the count , if count is = 10000 then commit it. Pl let me know your ideas and suggestion as soon as possible.
here is the small pl/sql script to delte the records with intermediary commits.
x number := 0;
for i in 1..5
x := x+500;
delete from dept where rownum < x;
that proc only 5 times in loop, i.e. if in ur table there are 2500 rows are there, then it will be delete 5 times, at each time it deletes 500 rows. If assume 3000 rows are there then still in ur table 500 records existed,i.e., its deletes only 2500 rows. so according to ur rows u may increase the loop capacity.
If loop 1..6 then all rows are existed, vice versa..