Strategy To Remove Old Records

Question

We are running Oracle 11.2.0.4.0. It is a transactional database that has been online since 2011. We have been tasked to clean up old records.

The table structure is (parent->child):

A->B-C->D->E

E has child tables F, G->H

All tables have dates on them but H. There are PK-FK relations for the table structure that is described.

Right now all tables except for G have around 70 million records in them. G has around 500 million records.

If I try I delete by joining the tables the query alone on a production backup takes several minutes to run just for the query. Also, the requirements for delete will remove several million rows at once. we have a limited time window to run deletes.

I've run some data analysis and currently the delete will remove about 40% of the data. Also, the system cannot be taken down for any extended period of time.

What's the best strategy for removing the old records?

Possible Strategy/Approaches:

1. Seven deletes using sub queries/joins to get the ids to remove. We would be joining up to 7 tables (lowest child).

2. Query each table for IDs to delete, store them in a temporary table. The use that temporary table get the records for the next child. Then once have all ids, to delete the records with something like this:

delete from H where PKID IN (Select PKID from tempH) delete from G where PKID IN (Select PKID from tempG)

Would there be any other possible alternatives? Most likely I would run this process several times deleting X amount of data. (Week's worth/Months worth, etc.) I don't think I can catch up with one massive delete.

Answer 1

It's not clear what you mean by "extended period of time" or why you associate deleting rows with "taking down" the system. If it's acceptable (as a matter of policy) to delete rows while the system is in production use, provided the deletions don't negatively affect performance, then the trick is to nibble away at the data.

Take some unit of the data -- an account, a day, a month, something "reasonable" -- as a "unit of deletion". The number of rows affected doesn't matter (as long as it's > 0); what matters is that the system can execute the deletion without "taking down the system" in terms of production response time. Write your procedure as one DELETE for each table, taking the deletion unit value as a parameter. You don't need to wrap them in a transaction if the process can pick up where it left off after an error. Write a loop to call that procedure for successive values. If desired, sleep between iterations.

For example, start with the oldest month. Remove associated data for H, G, F, E, D, C, B, and A. Get the next oldest month, repeat.

Since these are data you only want to remove, there's no data contention with the production system. It should be able to cook along happily. Provided your transactions aren't "too big", each individual deletion won't induce resource contention for very long. Choose well, and the production system should hardly notice.

But you might instead mean that you can't execute your deletion during working hours, that you'll be given the system for 30 minutes in wee hours to do your deed. That means removing the maximum row/second, a metric which is highly system- and database-dependent.

Likewise, whether it's a good idea to delete millions of rows all in one go is system dependent. Twenty years ago, that would have been preposterous on any system I'd ever seen. These days, it's routine on large databases. All depends on what you're working with.