What is the overhead of Referential Integrity on the database

A long time debate and a myth again today came in front of me; “Using Integrity Constraint on the database makes applications slower”

Please answer these two questions if you believe in this myth;
– Is your application only one that will access this data?
– How can you really believe that you can do these checks on client or middle-ware better than the database, where your data lives?

Here is a simple test case, again let the numbers speak;

Code listing 61 : Cost of Referential Integrity in the database

Result summary is as follows: (22.00-16.23)/196161=0,002941461350625251706506390159104 of CPU seconds per row for my test, worth for the integrity of your corporates data believe me..

Also, Mr.Kyte advices: “Are the consultants who are advising getting rid of the database constraint paid by the line of code they will produce” :)

Remember applications and technologies(cobol, client-server etc.) come and go, but your organization’s data lives forever!

Testing Information : the scripts mentioned are tested on Oracle Database 10g Enterprise Edition Release 10.2.0.2.0

Refences Used :
Effective Oracle by Design by Thomas Kyte Chapter 1-The right approach to building applications, Page 24

1 Comment

Leave a Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s