Home » Developer & Programmer » Designer » Refferential Integrities (Oracle DB)
Refferential Integrities [message #542483] Tue, 07 February 2012 07:59 Go to next message
Messages: 1
Registered: February 2012
Location: Princeton
Junior Member
Hi -
Currently we are building a big datamart. For our FACT table we have about 15 Foreign Keys joining with all DIM tables. Couple of DIM tables are pretty big. So enforcing the RI's while loading means we are compromising our loading time. Takes hours to load even small set of data. We have 0 SID in all our DIM tables.
Now the team is divided between having no RI and keeping the RIs. My question is "What's the best practice when it comes to having almost a billion record DIM tables. Is it still recommended to keep RIs. Or no need for it. Create a validation report after the fact load to find any data inconsistency?

Re: Refferential Integrities [message #542485 is a reply to message #542483] Tue, 07 February 2012 08:14 Go to previous message
John Watson
Messages: 8916
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum - I hope you will both learn and contribute. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

I strongly believe that you should always define every possible constraint. The more you have, the better Oracle will perform. I blogged about this recently, with a couple of trivial examples that demonstrate this:
If the RI constraints compromise your data loading, then look at tuning the process and the data structures, not removing the constraints.
Previous Topic: Random form freeze
Next Topic: Entity-Value-Pair vs 3rd Normal Form
Goto Forum:

Current Time: Sun Mar 03 00:55:52 CST 2024