Re: Data load ideas

From: jaromir nemec <jaromir_at_db-nemec.com>
Date: Tue, 4 May 2004 00:01:50 +0200
Message-ID: <022b01c4315a$3d20bd70$3c02a8c0@JARAWIN>

Original Message -----

From: "Tim Gorman" <tim_at_sagelogix.com>

To: <oracle-l_at_freelists.org>

Sent: Saturday, May 01, 2004 6:01 PM

Subject: Re: Data load ideas

> Why are you using foreign keys in a data warehouse anyway?

Hi Tim,

I completely agree with you on the topic of constraints, but I guess that this is not the only problem by exchange partition. What about the "read side" of this problem?

If there is a loading window in the data warehouse where no selects are permitted everything is OK. But while refreshing continuously you must take into account, that there could be some reading transaction at the time of exchange partition. Can those reading sessions expect the transactional behaviour (no dirty reads for example) of accessed data?

The problem is, in my opinion, that an exchange partition is a DDL, even thou used as a substitute for a DML, so the influence on a reading session could be - theoretically - the same as for example drop and recreate the table while reading it. It means somewhere between not really predictable and fatal.

I have experimented little bit with this stuff and saw interesting results e.g. combining serializable isolation level with exchange partition with validation, but I'm not completely sure if exchange partition is a legal publishing mechanism on a DSS without loading window.

In my opinion there are two different tasks implemented using exchange partition:
* total refresh of a snapshot (exchange old version by a new version of data)

maintenance of a rolling window (exchange partition is used add new data and to remove old data)

Any experience with exchanging partition while selecting the table?

Thanks

Jaromir D.B. Nemec

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Mon May 03 2004 - 16:59:11 CDT