Tom Kyte on constraints
I’ve put up some posts slamming what I regard as an absurdly extremist pro-constraints view held by some relational advocates. But Tom Kyte offers a more nuanced view of constraints that I find extremely reasonable.
The basic idea is this. Oracle has something called “non-validated constraints” — assertions that are true about the data but not enforced by the DBMS. Putting them in the database gives valuable information — metadata — to Oracle, allowing the optimizer to work much more efficiently. And there’s no performance overhead, because the constraints aren’t actually checked at run time.
As Tom correctly points out, this story assumes that you’ve indeed done a solid job of data cleaning. If not, the non-validation of the constraints could allow the system to give a wrong answer.
Comments
One Response to “Tom Kyte on constraints”
Leave a Reply
I’m a little surprised that Tom didn’t mention the declaration of dimensions as well, as they are a logical expansion of constraints that allow further flexibility for query rewrite. Theyu allow the declaration of multiple hierarchies within a data set and the specification of levels within the hierarchies and the data elements that are attributes of the different levels.
They are purely declarative and are not enforceable, although there are procedures for validating that the data does not contravene the declared rules.
Documentation here: http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14223/qradv.htm#BABDGGGB