Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

January 19, 2007

What is the overhead of Referential Integrity on the database

Filed under: Oracle Performance — H.Tonguç Yılmaz @ 10:27 am

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

About these ads

1 Comment »

  1. [...] as always, we need to test, calculate and decide on the alternative cost of using or not using any option available with Oracle for your own [...]

    Pingback by Constraints and how they may affect cost based optimizer’s choises « H.Tonguç Yılmaz - Oracle Blog — March 5, 2009 @ 12:03 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 80 other followers

%d bloggers like this: