In my application, we use Oracle 11g. We have tables holding large amount of financial information which grows on a daily basis. Our data is categorized into monthly data (basically monthly summary) and daily data (basically, daily holdings). Our purge requirements are something like 'In case of daily data, for the given month, retain the current and previous day's data and purge the rest'. For month end data, we retain data of the last & last but one business day of the month.
The challenge that we have is that the tables are of large size and have not been purged for the last couple of years due to few issues. This has slowed down application performance and hence we want to resume purge of the tables. However, in few regions, we would like to archive the data rather than purge it due to legal restrictions.
I guess partitioning might not work in this case as our purge requirements are pretty specific. Our purge programs are currently done programatically via PLSQL Procedures. What would be the best way to efficiently perform the purge / archival in this case? A purge frequency of once a week will suffice.