Alter table partition based on date and status
AB007Jun 19 2009 — edited Jun 28 2009Hi!
I have a strange scenario where in 100,000 to 500,000 records are created every day and records are to be maintained for 3 yrs. So to manage this table we have it partitioned based on date (so every day one new partition is created). And any partition older than 3 years are to be purged. But the catch is - the table has a 'Creation_date' and 'Close_date' parameter and as per my company's rule, I can only purge the 3 yr old data based on the 'Closed_date'. Basically we end up getting some entries with 'Close_date' that don't fall in the 3+ yr category and hence we just cannot purge the whole partition and reclaim the space.
What would be the ideal way to partition this table to make easy maintenance!?
eg -
Table_1: Partition_03152006
Col-1 Creation-date Col-2 Close-date
AAAA 03/15/2006 BBBB 03/16/2006 I can drop this entry+
XXXX 03/15/2006 YYYY 05/20/2006 I cannot drop this entry+
So, I am left with non-empty partitions which I cannot purge completely. Right now, I am moving these remaining entries to another partition and purging them when they complete the 3+yr time period.
Hope I could put in my query properly.
NOTE: RDBMS - ORACLE 10.2.0.4 on Solaris/SPARC 64Bit
Thanks in advance,
Arindam
Edited by: AB007 on Jun 19, 2009 1:15 PM