Deletions take a very long time

Hi,

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.

Badrianth

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
as
county number := 0;
COUNTY1 NUMBER :=0;
c_one account_delete_hlp.account_number%type;
cursor c1 is select account_number from account_delete_hlp;
begin
open c1;
loop
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
dbms_output.put_line(to_char(county));
end if;
if county = 1000 then
BEGIN
COUNTY1 := COUNTY1 + COUNTY;
commit;
county := 0;
END;
end if;
end loop;
close c1;
end;

Please suggest me a solution;
Badrinath

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.

P. Soni

COUNTY1 JUST TELLME HOW MANY RECORDS HAVE BEEN DELETED SO FAR !!!JUST ANOTHER COUNTER THAT'S IT!

NBNath

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
Badrinath

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.

Take care