Re: speedy deletes?

gplagge_at_hotmail.com wrote in message <7icall$fm4$1_at_nnrp1.deja.com>...
>Hi,
>We are trying to delete about 9 million rows from a table with over 40
>million rows, and which is activly in use (inserts, updates). The ideas
>we have come up with are verrrryyy slow. Any ideas on speeding them up?
>We have tried pl/sql looping through a million id numbers at a time
>(only deletes about 200,000 a day), regular deletes (forget it!), and
>cursor in pl/sql selecting a range of rows (slower than the first). We
>are deleting based upon an indexed field, and the explain plan says our
>cost is 5.
>
>Any other tips on speeding things up?

You are obviously trying to delete a LOT of rows from your table. And as you said, there is an index on at least one of the columns associated with that table. For every row that is deleted, the index(s) must be updated to reflect the change to the database. Most datawarehouses that want to delete (or insert) such a large quantity of rows to the table use this method.

1. Drop ALL indexes for that table.
2. Delete (or insert) the data.
3. Recreate the indexes for that table.

This is normally done during non-peak times. And you must know which indexes are used by that table. The above is normally done in "batch" mode as well.

Hope that helps
Brian Peasland
peasland_at_msn.com Received on Mon May 24 1999 - 21:47:46 CDT