Re: DELETE rate is inconsistent!

ibm_97_at_yahoo.com (JZ) wrote in message news:<10bc841c.0408110833.e0eda39_at_posting.google.com>...
> 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!

Why are you committing every 10000 rows? It does no real good, to rollback or to alleviate any ORA-01555 errors.

Simple SQL may be your friend here:

delete from <table>
where <where clauses here>
and rownum <= 10000000;

Give this a try and see if you have better 'performance'.

David Fitzjarrell Received on Wed Aug 11 2004 - 19:25:17 CDT