Archival & Retrieval operations

Hi Folks,

Have a scenario wherein have some 20 tables each with a million and more records. [ Historical ]
On an average I do add 1500 - 2500 records a day... i.e would add a million records every year on an average

Am looking for archival solutions for these master tables.

Operations on Archival Tables, would be limited to read.

Expected benefits

User base wud be around 2500 users on the whole - but expect 300 - 500 parallel users at the max.

Very limited usage on Historical data - compared to operations on current data

Performance on operations over current data is important compared over that on historical data

Environment - Oracle 9i - Shud be migrating to Oracle 10g sooner.

Some solutions i cud think of ...

[ 1 ] Put every archived record into a archival table and fetch it
from there
i.e clearly distinguish searches as current or archival - prior to searching
the impact i feel is again archival tables are ever increasing by approx a million in a year

[ 2 ] Put records into various archival tables each differentiated by
a year
For instance every year i do replicate the set of tables and that year data goes into that table.
how do i do a fetch??
Note - i do have a unique way of identifying each record in my master table - the primary key is based on YYYYMMXXXXXXXXXX format eg: 2008070000562330, will the year part help me in anyway to check with the correct table

The major concern is i do have a very good response based on indexing and other common things, but would not want this to downgrade in a year and more, but expect to improvise on the current response timings and also do ensure to conitnue the same over a period of time.

Also I dont want to make change to every query in my app - until there is no way out..

Eager to hear out from you. Thanks.. KS. Received on Wed Aug 22 2007 - 20:40:23 CDT