Thanks for the question, Arslan Afzal.
Asked: June 10, 2024 - 7:06 am UTC
Last updated: June 10, 2024 - 12:27 pm UTC
Version: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Viewed 1000+ times
You Asked
Hi,
I will be starting my development for an Archiving project. Aim is to move all the old data from Production database and make it lighter. It consist of 2 parts. One is to move all data except the latest 6 months into an Archiving database in Oracle from PROD DB. Other step is to remove the all data except the latest 6 months from Production DB.
I am doing some brainstorming on the step2 which is to delete data from Production DB. Now the size of DB is around 4 TB in PROD. Around 3 TB of the data has to be deleted from Production DB. Now the scenario is, that while bringing data from PROD to Arch DB, we removed FKey constraints and some indexes too. This cannot be done in PROD. Secondly, the application will be live. We are in discussion for a window where the app can be shut down for some time so we can work on it.
Based on above situation, where we have heavy tables having 5-6 years of data and we need to delete almost 85-90% of data from these tables (around 130). What options do I have? Since its production, our aim is to delete in minimum possible time and minimizing the possibility of effecting the PROD application.
and Chris said...
Here are a few options:
- Create-table-as-select to build a table only storing the rows you want to keep, then switch the tables over
- A filtered table move that "deletes" the data - this assumes it has no child tables
- Partition the table and drop the old partition(s) - Will make ongoing archiving easier; requires the partitioning option
Exactly which you use depends on your constraints and which trade-offs are most important to you. I dived into the details of these in an Office Hours session a couple of years ago:
Or read this blog post for more discussion on deleting lots of data fast
https://blogs.oracle.com/sql/post/how-to-delete-millions-of-rows-fast-with-sql