0

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.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Venky
  • 1
  • 2
  • Why wouldn't partitioning work? I don't see how specific requirements would create problems there. Of course, that would involve a pretty substantial application redesign which would require a fair amount of analysis. Plus partitioning is an extra cost option on top of your enterprise edition license. But I'm not seeing any technical reason that partitioning would be problematic. Can you explain exactly where you envision partitioning being problematic? – Justin Cave Dec 16 '14 at 11:02
  • I think partitioning can be used as an additional feature, to gain performance, but fitrst you got to get the removal and archiving working correctly. You can use PL/SQL for that, but even SQL will do. There's nothing preventing you from performing a delete, but ONLY of records that are archived in some manner. You do not need a PL/SQL cursor for that, although you can. Archival is just a bit more tricky, since you got to ensure no data is lost, but that should be not that difficult. – tvCa Dec 16 '14 at 17:09
  • @JustinCave: Thanks for your feedback. I am not very informed on oracle partitions. As I understand it, we can partition a table say based year etc. where we can say that all data for 2012 goes to partition A, 2013 goes to B and so on. In my case, today's data becomes obsolete in the next 2 days. So how can partitioning be done in this case? – Venky Dec 17 '14 at 08:20
  • @tvCa: PLSQL or SQL will work for purging data. Thats what we do now in few regions where archival is not a requirement. However, via SQL script, purge takes a very long time given that we have multiple tables with large data in it. Is there any way that is more efficient than PLSQL for purging? – Venky Dec 17 '14 at 08:22
  • @Venky - It would seem like daily partitions would solve your problems. You'd have to write some code to figure out which partitions to purge, of course, but dropping a partition is going to be basically instantaneous. Of course, that doesn't begin to do the analysis on what, if anything, might break if you re-designed all your tables to turn them into partitioned tables. – Justin Cave Dec 18 '14 at 00:36
  • @Venky.. I do have a similar situation.. Could you please tell me if you achieved something at your end? Why can't I use partitioning at my end - Reason being, for e.g. if I have daily partitions and suppose I have 100 records in that partition, I need to archive only 50 records out of that partition due to some business filters. So, I guess, I can't use partitioning method in my case.. Could you or someone pls help with the approach on how we can achieve this? – Vaibhav Gupta Apr 04 '20 at 07:44

0 Answers0