Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Archival & Retrieval operations

Re: Archival & Retrieval operations

From: DA Morgan <damorgan_at_psoug.org>
Date: Wed, 22 Aug 2007 20:31:18 -0700
Message-ID: <1187839876.685962@bubbleator.drizzle.com>


skarthik143_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
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Wed Aug 22 2007 - 22:31:18 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US