We are running Oracle 18.104.22.168.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):
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?
Seven deletes using sub queries/joins to get the ids to remove. We would be joining up to 7 tables (lowest child).
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.