February 5, 2006

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.


One Response to “Tom Kyte on constraints”

  1. David Aldridge on October 3rd, 2006 12:11 am

    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

Leave a Reply

Feed: DBMS (database management system), DW (data warehousing), BI (business intelligence), and analytics technology Subscribe to the Monash Research feed via RSS or email:


Search our blogs and white papers

Monash Research blogs

User consulting

Building a short list? Refining your strategic plan? We can help.

Vendor advisory

We tell vendors what's happening -- and, more important, what they should do about it.

Monash Research highlights

Learn about white papers, webcasts, and blog highlights, by RSS or email.