Re: What is the cost of integrity constraints?

From: <roelof.streekstra_at_gmail.com>
Date: Thu, 16 Apr 2009 07:20:06 -0700 (PDT)
Message-ID: <294b84bc-d8e1-4ae3-8abe-d3e7c8fa91ab_at_p4g2000vba.googlegroups.com>

On Apr 16, 1:32 am, sybra..._at_hccnet.nl wrote:
> On Wed, 15 Apr 2009 21:23:17 -0700 (PDT), Phper
>
>
>
> <hi.steven..._at_gmail.com> wrote:
> >On Apr 15, 12:50 pm, roelof.streeks..._at_gmail.com wrote:
> >> On Apr 15, 12:13 am, Phper <hi.steven..._at_gmail.com> wrote:
>
> >> > Does the database run much slower if there are many integrity
> >> > constraints?
>
> >> Yes it runs slower.
> >> But the question you ask is WRONG!!
> >> Most databases do more reads then writes so why would you optimise for
> >> inserts instead of guaranteeing that the data is correct.
> >> Most business people value correct data over speed of inserts.
>
> >> Thanks roelof.
>
> >> PS One of the corner stones of Relational Databases theory is having
> >> constraints!!!!!!
>
> >But we have an option. We can adopt triggers.
>
> Which is about 8 times less efficient as constraints and triggers are
> always parsed.
> This is not an option. This just one of the ways to end up with an
> unscalable app.
>
> --
> Sybrand Bakker
> Senior Oracle DBA

I agree completely with Sybrand here.
Use regular check constraints and referential Constraints that the database provides.
Only use triggers for constraints you can't define like constraints across rules like:

• average salary by department cannot exceed X (3000).

But the problem with those triggers they are not easy to write so you might not want to write them.
see this blog entry tom kyte: http://tkyte.blogspot.com/2007/03/dreaded-others-then-null-strikes-again.html

Have way through this blog you see the solution that is used to implement that trigger.
The person that wrote that trigger is the same person who wrote the book and is also a recurring guest speaker at HOTSOS.

Thanks Roelof. Received on Thu Apr 16 2009 - 09:20:06 CDT