Re: Archival & Retrieval operations

On 23 Aug, 13:31, DA Morgan <damor..._at_psoug.org> wrote:
> skarthik..._at_yahoo.com wrote:
> > 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.
>
> If Enterprise Edition institute partitioning and leave the records
> alone. If not then tell us more about your environment and what you
> are trying to accomplish.
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -

We are using the following environment.

Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.8.0 - Production Received on Wed Aug 22 2007 - 23:10:00 CDT