Karthik wrote:
> 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
Then I would suggest talking with your sales rep about the cost
of getting the partitioning option. If you partition your date
by date you can set each partition in its own tablespace and when
they age out just set the tablespace to read only.
If the archival data is moved to a different machine then you can
easily use transportable tablespaces to quickly move the partitions.
--
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 Thu Aug 23 2007 - 11:28:18 CDT
