Forgot to tell you, Deleting even thousand records take as long as 10-14 minutes.
Here is the procedure ,I use to delete records;
create or replace procedure del_rec
county number := 0;
COUNTY1 NUMBER :=0;
cursor c1 is select account_number from account_delete_hlp;
fetch c1 into c_one;
exit when c1%notfound;
delete from acc_partition partition (part02)
where account_number = c_one;
county := county + 1;
if mod(county,1000) = 0 then
if county = 1000 then
COUNTY1 := COUNTY1 + COUNTY;
county := 0;
I've facing this problem before, mod() really affected not so much based on this condotion, just use counter to record it if you want to replace it. Should you create index on the column in where clause will boost you operation time, of course this experience i got is not for partion table, but at least you can try that. If this not help, probably there is some other things involved.