--CELKO-- wrote:
>>In a data warehousing application, what is the impact of imposing referential integrity on the database side? <<
Unlike OLTP, a warehouse is scrubbed BEFORE it is persisted in the
schema and then it is STATIC. There is not (well, should not be) any
need to have RI or a lot of constraints. What you want ina data
warehouse is access methods for aggregation of bulk data. I havea
book on OLAP & ANALYTICS IN SQL due out in a few months which gives an
over view of the issues.
While what Joe says is correct wrt constraint enforcement the DB2
optimizer can make very good use of constraints.
To solve these conflicting directions Db2 supports "informational
constraints" that is you can define constarints (check and RI) as "NOT
ENFORCED" but "ENABLE QUERY OPTIMIZATION"
This enables rules such as a theorem prover for check constraints and
"RI-Join-Elimination" to kick in.
Also it is recommended to specify UNIQUE indexes for these RI.
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/