Re: Help Designing a Billion rows System
From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 04 Aug 2006 11:48:51 -0700
Message-ID: <1154717332.260365_at_bubbleator.drizzle.com>
>
> Dual databases - a 'loading database' with a 'single partition' table
> specifically for retrieving & cleansing the data; and a 'production
> database' that receives the prepared partition using transportable
> tablespaces.
>
>
> Seems reasonable. 60 partitions are not an issue other than you needing
> to manage them.
>
>
> I recommend reading the concepts manual to get a better understanding of
> partitioning. For 10gR2 it's
> http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14220/intro.htm#sthref201
>
>
>
> Also - check out metalink.oracle.com for concerns about partitioning in
> your preferred version. (Since this is production, I assume you have
> apprpriate licenses and therefore have access to metalink.)
Date: Fri, 04 Aug 2006 11:48:51 -0700
Message-ID: <1154717332.260365_at_bubbleator.drizzle.com>
HansF wrote:
> On Fri, 04 Aug 2006 09:26:55 -0700, dpafu wrote:
>
>> What approach do you recommend?
>
> Dual databases - a 'loading database' with a 'single partition' table
> specifically for retrieving & cleansing the data; and a 'production
> database' that receives the prepared partition using transportable
> tablespaces.
>
>> I was planning to have a partitioned table per day (is it possible to >> have 60 partitions?), use a PK on those unique values, use external >> table (or SQL*Loader) to load the data and use the HINT APPEND to >> insert new data. Then use another process to drop old partitions and >> create a new one for today's data.
>
> Seems reasonable. 60 partitions are not an issue other than you needing
> to manage them.
>
>> I'm not very familiar with partitioned tables, so any example and >> help is appreciated.
>
> I recommend reading the concepts manual to get a better understanding of
> partitioning. For 10gR2 it's
> http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14220/intro.htm#sthref201
>
>
>> What do you think about this approach? >> Any other ideas?
>
> Also - check out metalink.oracle.com for concerns about partitioning in
> your preferred version. (Since this is production, I assume you have
> apprpriate licenses and therefore have access to metalink.)
I concur with Hans with one caveat. How is this table going to be queried? By day? By week? By minute and second?
It is not uncommon for banks and telephone companies to have a partition every 5-15 minutes and keep a year's worth of data on-line.
Also look at hash subpartitioning. Might it help with retrieval?
-- Daniel A. Morgan University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.orgReceived on Fri Aug 04 2006 - 20:48:51 CEST