Re: Data load ideas

  • From: Tim Gorman <tim@xxxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Sat, 01 May 2004 10:01:09 -0600

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

The only technical reason for FKs in a DW is when one is trying to use
materialized view query-rewrite and one is utilizing QUERY_REWRITE_INTEGRITY
= ENFORCED to prevent MV "staleness".  Otherwise, there is no technical
reason for FKs, as there is no need for RI to be enforced in the DW, because
the DW is not the source-of-record for the data.  Validation reporting is
much more effective than enforced constraints.

Just my $0.02...



on 4/30/04 6:33 AM, jtesta@xxxxxxxxxx at jtesta@xxxxxxxxxx wrote:

> I did some testing since i'm going to implement something like that for
> the warehouse project i'm working on.
> 
> what i'm finding is when i create the table to hold the data(that will be
> exchanged from), if i add the FK to that table before the exchange is
> done(and everyone is enable validate right now), i have no problem doing
> the exchange.
> 
> so i must be missing something here in the thread, what is it?
> 
> joe
> 
> ****original message below ****
> 
> 
> Thanks--but what happens in the presence of enabled foreign keys that
> reference these PKs, or enabled FKs in the tables to be exchanged
> that reference other tables?  I am finding that I can't do the exchange
> unless all FKs are disabled.  Will perform further tests tomorrow and
> submit results.
> 
> --- Jacques Kilchoer <Jacques.Kilchoer@xxxxxxxxx> wrote:
>> No, after the exchange the indexes are not left in an unusable
> state.
>> See example below.
> 
> 
> 
> 
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: