1

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.

  • If you can delete the data using a single query, that should be the most efficient approach. I'm not sure why the fact that the query runs for a few minutes is a problem for you. Unless you are concerned that some other process needs to update the data that you are in the process of deleting, which seems like a poor requirement, I'm not sure that there is a problem to solve. – Justin Cave Feb 19 '16 at 15:07
  • @Justin - I would only have a certain amount of time to perform the maintenance. Just concerned about a long running process. I have 8 tables so by single query I think you mean 8 queries/deletes. Would you be concerned about deleting several million rows at once in one transaction? – Jon Raynor Feb 19 '16 at 15:12
  • 1
    It could be 8 queries, it could be 1 depending on things like whether the foreign key constraints are set to cascade deletes. You'd need to ensure that things like your undo tablespace were sized to handle this one very large delete. You may want to shrink the tables as well (possibly in a later maintenance window if you don't have time in this one). Ideally, you would have partitioned the tables and then dropping old partitions would be nearly instantaneous. – Justin Cave Feb 19 '16 at 15:17
  • @Justin - Thanks, the constraints are not set to cascade delete , so it will be multiple deletes. I will check with the DBA about the undo table space size. Partitioning is something I will mention as well for the future. – Jon Raynor Feb 19 '16 at 15:27
2

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.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.