Re: Help Designing a Billion rows System

[Quoted] On Fri, 04 Aug 2006 09:26:55 -0700, dpafu wrote:

> What approach do you recommend?

[Quoted] 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.

[Quoted] 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.

[Quoted] 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?

[Quoted] 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.)

-- 
Hans Forbrich   (mailto: Fuzzy.GreyBeard_at_gmail.com)   
*** Feel free to correct me when I'm wrong!
*** Top posting [replies] guarantees I won't respond.