Re: quick FK question
Date: Wed, 23 Dec 2009 08:46:57 -0800 (PST)
Message-ID: <993618.67090.qm_at_web113213.mail.gq1.yahoo.com>
Rumpi,
Your right, it's common to have FK enforcement occur at the integration (ETL) point or even just assume that the system of records are correct and not worry at all about the relationships (that's a dangerous one!). However in later versions of Oracle (10g and later as I recall) the optimizer will depend on defined FK relationships when generating execution plans and if they are not defined, you may not get an optimal execution plan. To avoid the problems with validation, you can define FK's but not have them be enforced. Also, FK's help define specific business rules that modelers that come later might well benefit from having documented in the metadata of the database. So, if it were me I'd be defining non-enforced FK's on my warehouse regardless of where (if) I'm really enforcing those relationships.
RF
Robert G. Freeman
Oracle ACE
Ask me about on-site Oracle Training! RMAN, DBA, Tuning, you name it!
Author:
Oracle Database 11g RMAN Backup and Recovery (Oracle Press) - ON ITS WAY SOON!
OCP: Oracle Database 11g Administrator Certified Professional Study Guide (Sybex)
Oracle Database 11g New Features (Oracle Press)
Oracle Database 10g New Features (Oracle Press)
Other various titles
Blog: http://robertgfreeman.blogspot.com
Check out my new blog series on installing Oracle Database 11gR2 on Windows using VMWare!
From: Rumpi Gravenstein <rgravens_at_gmail.com> To: yong321_at_yahoo.com
Cc: gints.plivna_at_gmail.com; oracle-l_at_freelists.org Sent: Wed, December 23, 2009 6:48:14 AM
Subject: Re: quick FK question
Gints,
There's another aspect to this conversation with regards to the fact that for warehouse implementations foreign key constraints are often times *enforced* in the ETL process and not in the database definition. Insofar as that's the case, the extra expense you describe in checking that foreign key relationships tie back to a parent record row isn't incurred.
Rumpi
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Dec 23 2009 - 10:46:57 CST