Hi Nancy,
Unless you are having serious performance / storage problems or another valid option would be just to delete the data, don't do it.
I inherited a database where we archive data yearly. I started working on the checklist and scripts last month and we will do it on the first weekend in October.
For each of our 'main' tables, we have 5 'history' tables. To put it simply, I exp the oldest history tables; rename all the other history tables to an older name ( _3 becomes _4; _2 to _3, etc.); create a new set of _0 tables; and then move some of the records from the main tables into the new _0 tables. It's actually harder than it seems with constraints, primary key, etc. Also if you change one of the main tables any time during the year, you have the make the same change to 5 other tables.
Originally the history tables were only going to be used when they needed to look up some old stuff. Now they are used all the time. In fact since there are some parent and child tables combinations, we have Union queries with 6 select statements for a total of 12 tables. SLOOoooooow. Having all the related data in one table would have to be faster. Partitioning would be a better answer yet.
Remember the exports of the 'old' data that I mentioned before? Sometimes they want to look at that stuff too! We get to imp it back into the database and then delete it when they are done. What fun! In fact they were just discussing creating _5 tables which would lead to _6 next year and so on. We go back 5 years and they want to look at it. Only 3 years seems a little recent to me.
Fixing it all would make sense; however, that would require rewriting tons of code. At least we have all the history tables in one tablespace now. In the past all the _0 tables and indexes would be in their own tablespace; the _1 in there own.....
I'm a firm believer that archived data is lost data. What if you put in on a media that isn't supported anymore or you can't find the right hardware? Or migrate to another database?
Jerry Whittle
ASIFICS DBA
NCI Information Systems Inc.
[EMAIL PROTECTED]
618-622-4145
-----Original Message-----
From: Nancy Hu [SMTP:[EMAIL PROTECTED]
We have some tables that have data for many years. We are going to archive
the data that are older than 3 years. I would like to find out how you guys
usually do this or a best way to do this. Thanks for any inputs in advance.
Nancy
