I have created partitions, re created the indexes, am using a large rollback segment, have set the table for nologging
and also commiting every thousand records.....
All these I am doing to delete around 2lakh records from a table.
What other parameters should I check to make the deletions faster, Now the deletions are taking a very long time and testing our patience.
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;
Please suggest me a solution;
use truncate instead of delete, much fast
first, how many lines do you update?
second, haveing any index that supports "where account_number = c_one; "? if no, create it, if yes, try to rebuild it.
therd, check function "mod(county,1000) " (to me it looks like a function) for preformance problems. see if your probrm is there.
and last, check you cursor, you may find the select statment there to be preforming very bad. check index in your where clouse, rebuld theme if you need.
I am not getting
1. why mod() function has been used,
while you can use 'if country = 1000'
It will never cross the 1000 becuase
u r reset country = 0 when country = 1000;
2. why are you using country1 ?
If i am unable to understand please explain.
COUNTY1 JUST TELLME HOW MANY RECORDS HAVE BEEN DELETED SO FAR !!!JUST ANOTHER COUNTER THAT'S IT!
I have removed the mod() function and 'am using 'IF' condition.
Deleting 4000 records seems to take over 2 hours. The deletion happens on a partition of a table.
In the v$session, I see one session as inactive. Is this causing any problem
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.