Partitioning or Backup Tables...?
By Jean-Pierre Dijcks on Aug 19, 2009
Satish is proposing (or forced) to create backup tables instead of using partitioning. I'm not sure you want to go there.
If you do, I think you will need to create views to show the data as a single entity, this means that you need to change the application to read from the views rather than from the original tables (or rename the tables first, then create views with the original names). You then want to compress the backup tables.
So you do a CTAS with a filter to create your backup tables from the original ones, add them to views and you are in business. You also just built the same as we do with partitions and how we would recommend doing database ILM strategy.
In other words, you are creating an ETL process that runs once a year (or at whatever interval). You will lose out on transparency, you will also lose out on maintenance, you will get a more complex system.
If you use partitioning, you will also get the benefits of faster stats gathering (in 11g - using incremental stats and the new Synopsis we use there) and potentially of data loading.
So the choice is to either make this transparent and get various other benefits - but pay some money. Or build it all yourself...
Look at these posts:
Any who, one other resource to look at are the OTN Forums for questions like this.