Delete 3 million records!!!

[richardhk]

I would like to ask if any guys have a good strategy (e.g. fallback plan, implementation) to delete 3 million of records from 3 tables using PL/SQL.

How long would it take to do that???

Many thanks in advance!

 

[jaggiebunnet]

How long is a piece of string?

Firstly, don't use pl/sql if it can be avoided. Use SQL instead. Size your rollback segments accordingly and run the delete statements. It shouldn't take that long depending on resources available, other processes running etc; 3 million records is not that much really.

 

[sem]

Actually it may depend on how much records you plan to leave. It expected amount is far less than 3 mln, you may consider storing them into temporary table (by CREATE TABLE AS SELECT), truncating original table and then moving them back. In any case it would be nice to drop indexes not suitable for searching records to delete (recreate them later) and ALTER TABLE NOLOGGING. Don't forget to choose proper RBS, because expanding it during DELETE processing is one of the most time-consuming operations.

Regards, Dima

http://seminihin.narod.ru