Constraints

Constraints do just that; when enforced they constrain data such that rules are obeyed. But even when not enforced the existence of an Oracle RELY constraint does something really useful, it tells the CBO, and especially the query rewrite engine, that certain facts about the data can be presumed to be true. Of course, if we don't tell the truth to the database we can into all sorts of problems.

But why should we want to tell the database to trust us, wouldn't it be better to let the database examine the data and use, real, enforced constraints? Real unique constraints are enforced by b-tree indexes, and for large fact tables, this can be a problem; typically we would need an index based on each dimension column in the fact table and furthermore for a partitioned table this should be a global rather than local index. In one of my systems we have a 600 million row table spread over 800 partitions and each row is uniquely defined by a combination of 10 dimensional attributes. Having an index of that size is no joke - we need masses of storage; we either slow data load as each item is checked against the constraint or we spend time rebuilding the constraint post data load. And to what purpose, it is not as if we would often run queries that target specific rows by way of the composite index.

For my money, real constraints are used in the staging process to validate data; when the data is proven clean and published to main data warehouse it is the time for the rely constraints to take over.