| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Data load ideas
Why NOT have FKs in your data warehouse? I realize its not the system
of record, so to speak, but having RI constraints enabled after a data
load is a nice way to confirm you didn't just load up any bad data.
What is the harm in having RI enabled? Lets say I'm doing my data loads to the warehouse through transportable tablespaces, and then the alter table ... switch partition method described in teh Oracle DW guide. AFter that's done, I rebuild indexes, re-enable RI constraints and I get a great quick check to confirm nothing unexpected just got loaded.
Is there any overhead generated by leaving FK checks in place, once you have data loaded in? Its just a rule-set check, right? They'd never get "fired" on pure selects, and we're never updating or inserting transactionally into the DW.
Also, If you don't have index-supports for your natural FK-relationships between facts and dimensions, how would you ever resolve a common dimensional query? We have bitmap indexes supporting every fact-dimension relationship (which are technically foreign keys).
I'm not trying to be difficult; i'm just wondering if my DW implementation is wrong or needs serious thought.
Todd
>
> 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_at_dmc-it.com at jtesta_at_dmc-it.com 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_at_quest.com> 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_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
> > -----------------------------------------------------------------
>
> ----------------------------------------------------------------
> 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
> -----------------------------------------------------------------
>
-- 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 Sat May 01 2004 - 11:07:07 CDT
 |
 |