Hello ASK TOM Team.
I have a business requeriment to clean test data (almost 50 tables related to each other) from a sandbox enviroment. The data to be deleted is by user (a value in a column called userid). The retention for each user is 30 days. Some tables could have million rows. When I say "clean" I mean delete because the same data would be send again and if is not deleted users cannot send their test because of business rules.
I know deletes are too expensive and could fragment tables in the database.
What would be the best aproach to do this keeping the performance and the system online?
Keeping in mind the answer to the question above, what method can I use to automatically delete records older than 30 days?
Thanks in advanced.
Regards,
When I say "clean" I mean delete because the same data would be send again and if is not deleted users cannot send their test because of business rules. I don't understand what you mean by this - an example would help.
While deleting millions of rows can take a long time, the system will still be "online" while this happens. Where you might run into trouble is the deletes blocking other updates/deletions accessing the same rows (which begs the question why are you trying to do this!) or generating lots of redo and running out of archivelog space.
If you want to remove all data 30+ days old, creating daily partitions on insert date may be the best method. This enables you to wipe all the expired data by dropping the relevant partitions.
I cover various other ways to speed up "deletes" at:
https://blogs.oracle.com/sql/post/how-to-delete-millions-of-rows-fast-with-sql what method can I use to automatically delete records older than 30 days?Ultimately you'll need to set up a job or other process that removes the required data.