My team is in sort of between a rock and a hard place. The many examples we have read through on partitioning, none of which fit our apps data model has left us with more questions than answers. Since we are not centered on sales etc... we do not have data that can be archived off.
Our data is contact relationship, contacts come in they get communicated with etc... and many just go dormant. We normally only use the last year to year and a halves worth of contacts, so if we query off of our last modified date we can see about 1/4 or less of our contacts are active.
So one thought was to create partitions where we take each year end as a boundry. So our boundries would be 2002-12-31, 2003-12-31, 2004-12-31, 2005-12-31, 2006-12-31, 2007-12-31, 2008-12-31, 2009-12-31, 2010-12-31, 2011-12-31, 2012-12-31, 2013-12-31.
Our thinking was if we created the future partition of 2012 and possibly 2013 that we could go forward without having to repartition for a while. Here in lies our dilehma, we can have old contacts whose last modified dates change due to being reactivated as a current active contact.
Will that row then be removed from a partition say 2009-12-31 into the current 2011-12-31 partition? We are assuming that it would so that our partition design stays in tact.
If not how can we partition these table in order to streamline our queries? Right now our servers are taking a pounding sifting through millions of rows of dead data.
We have static id's and the contacts creation date we could partition on. But we believe we would end up in the same performance we have now because we have current active contacts throughout the entire table. Thus we would end up querying across most if not all of the partitions.
We would like to limit the 90% plus of all of our queries to end up on no more than the last 2 partitions say 2011 and 2010.
So we feel kind stuck in this middle gray area that we cannot find any examples on.
Your guidance is much appreciated