Re: Data warehouse - one main archive?

Daily partitions on 'years' of data may give you unpleasant side-effects of too many partitions - test carefully.

It is not necessary to have two schemas, but may reduce the risk of 'bleed-over' from queries happening when you are loading
data.

An easy option for loading/unloading is simply to

create new partition
create empty table
load table
create indexed on table

'exchange partition including indexes excluding validation'

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

pearsonj5998_at_my-deja.com wrote in message <889dsq$qih$1_at_nnrp1.deja.com>...
>I have a non-Oracle data warehouse implemented in 2 logically identical
>STAR schemas. One holds the data for the current day, the other is the
>main archive, holding data for years. An 'unload | load' operation is
>performed every night.
>
>This design was selected due to minimise contention selecting 'old'
>data to the batch loading of current data (every half hour). Approx 30-
>40 Million records are added per day. Queries are generally not
>directed at the current day's data.
>
>I am porting to Oracle8i and want to partition the main archive into
>daily partitions. I want to use Direct Loader in fastest possible way
>and do not require transactional rollback.
>
>1) Is it really necessary to have two schemas in this context?
>2) If it is, what is the fastest way to perform an Unload-Load
>operation?
>
>
>Thanks,
>JP
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.
Received on Mon Feb 14 2000 - 13:27:13 CST