Re: Deleting Large number of Rows

Would be much better if you could process multiple rows in a single SQL statement, then commit. Maybe you could write a pl/sql block that opens a cursor on the table that has the list of rows to delete, then commit every 500 or something. Take advantage of array processing.

If you get smarter, you can divide the deletes up and batch off several processes deleting rows concurrently.

If you are deleting a lot of the table, you could drop the indexes, do the delete (driving of the delete table), then rebuild the inndexes. As you will find deleting the index values will take much longer than deleting the rows.

Another alternative is to copy the rows you are NOT deleting to a new table, then drop the original table and rename the temp table.

Andrew.

Peter Stryjewski wrote:
>
> How can I quickly delete large numbers of rows?
>
> Currently, I have a list of "index" values (that are a retreival key).
>
> I "pipe" these commands into a sqlplus session.
>
> delete from <table> where <index> = value;
>
> after 5000 deletes, I put in a commit;
>
> These aren't really grouped together, so each one is a separate delete
> statement.
>
> Pete Stryjewski
> pstryjew_at_worldnet.att.net
Received on Thu Feb 18 1999 - 21:36:48 CST