Re: speedy deletes?

Gene,
  you might want to conisder a mixture of technical   and functional solution.

1. try to use table partitions. Dropping a table partition is very fast
2. try to use a reverse indexes (not descend (!)), this should give you a better reuse of the index, it doesn't grow too big, hence less IO
3. you don't mention if you do a simple DELETE or if you are using subqueries in your WHERE-Clause
4. Introduce a "Delete Flag", this flag is set when the row has to be deleted, the application (you could use fine-grained access control (contexts) introduced in 8i, so you don't need to change the application if you don't want to) would exclude that rows from the queries. A periodical delete-job would remove the rows at a point in time with low workload
5. Use ON DELETE CASCADE for foreign key constraints
6. Use the DEFERRABLE clause for your index and the delete transaction.
7. run a trace when deleting ... see where you loose your performance
8. Keep monitoring the exclusive locks when deleting
9. Use the parallel clause (if it makes sense in your environment)

Regards
Dante

In article <7icall$fm4$1_at_nnrp1.deja.com>,   gplagge_at_hotmail.com wrote:
> 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?
>
> Thanks,
>
> Gene Plagge
>
> --== Sent via Deja.com http://www.deja.com/ ==--
> ---Share what you know. Learn what you don't.---
>

--== Sent via Deja.com http://www.deja.com/ ==-- ---Share what you know. Learn what you don't.--- Received on Tue May 25 1999 - 05:07:50 CDT