DELETE rate is inconsistent!

Oracle 9205 on Red Hat Advanced Server 3.0

We have a nonpartitioned table which has 48 millions rows. We want to delete 10 millions rows. And we commit once every 10000 rows being deleted:

The PL/SQL is like:

for v_counter in 1..numberofloops loop
delete from <table>
where
<some where clauses>
and rownum <= 10000;
commit;
end loop;

Initially (first 5-6 millions) the rate for DELETE is ok, it's like 2-3 minutes for every 10000 rows, then the rate dropped a lot to 15-20 minutes for every 10000 rows.

Is there anything wrong with our PL/SQL? Any other better ideas to do the same thing?

Thanks a lot! Received on Wed Aug 11 2004 - 11:33:17 CDT