Re: massive delete

Hi Jorge,

   Two suggestions :

1. Rather than the cursor, you could delete with the help of the rownum variable as follows:

  SQL> delete from massive_table where rownum<10001 and ....;

   This will delete the first 10000 rows satisfying your criteria defined after the and ... above.

2. If you want to delete all rows in the table use the truncate tablestatement:

   SQL> truncate table massive_table;

   This won't take more than a few seconds and will free up space too, but beware : it's a non-recoverable DDL and you cannot use it unless you disable all referential constraints on the table you are operating on.

Hope that helps,
Chetan
Bangalore, India

Jorge wrote in message <7sd3m6$6fi$1_at_diana.bcn.ttd.net>...
>Hi:
>
> Which is the fastest way to delete many rows (over 400k-800k) from a
big
>table (over 7.000.000 rows) frequently.
>I'm doing it now with a cursor to the table, committing every 'N' rows.
>
>Will performance get increased with bigger rollback segments and reducing
>committing?
>
>excuse my poor english
>
>thanks
> Jorge
>
>
Received on Thu Sep 23 1999 - 07:15:36 CDT