Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Delete Data older than exactly 2 years

RE: Delete Data older than exactly 2 years

From: Mark W. Farnham <>
Date: Fri, 20 Apr 2007 10:18:30 -0400
Message-ID: <013801c78356$c57bcec0$>

I think a lot of industries work off comparitive week analysis, and that no particular calendar time grouping is *always* better than another.  

Cohesion of weeks may even be more important than cohesion of years to the extent that in some industries years are defined as 52 weeks most years except when the mismatch between 365.25 and 52*7=364 adds up to needing an extra week about every 5.6 years on average. In fact weeks are consistently of the same duration if you allow exception for leap seconds and the like, while months vary wildly in duration from 28 days to 31 days (albeit in a predictable pattern).  

Matching your archiving time frame to your delete (or partition unplugging) strategy and basing that strategy on the natural or accounting rhythms of the data collection system is often a good choice for information life cycle management. This works especially well when the periodicity of the cycle is long enough and the oldest data is cold enough that the chance it cannot be archived clean or graduated to long term lower priority access before it needs to be actually discarded from the system being pruned is vanishingly small.  

On the other hand, if the information grouping of the company for a particular system is month, then it makes sense to drive your information life cycle off months. I'm actually a bit curious what is driving the need for it being "exactly" two years.  

As a general principal I encourage the notion that folks think strategically about how they will handle the disposition of data in the long haul as a priority concern when the system is designed. There is a strong anti-correlation between this type of thinking and the type of system that runs just fine for a few months or a few years after it starts accumulating data and then hits a crisis of size that may be difficult to escape like a box canyon during a gully washing cloudburst. It should also drive policy concerns about how to handle manipulation of old data when the incumbent schema is improved, but I've already written too much and I'll just leave that giant topic dangling.  



From: [] On Behalf Of rjamya
Sent: Thursday, April 19, 2007 10:17 PM
Subject: Re: Delete Data older than exactly 2 years  


Month to month cleanup is always better than on a random date in the last week. What do you think?  


Received on Fri Apr 20 2007 - 09:18:30 CDT

Original text of this message