Re: Foreign Keys as to affect impact on performance
Posted by: Rick James
Date: November 12, 2014 07:23PM

Embellishing on Peter's comments...

When INSERTing, the FOREIGN KEY CONSTRANT involves reaching to the other table and doing a lookup in an index to validate the constraint. This lookup costs something. In huge tables it can cost a lot if it needs to hit the disk.

The INDEX mentioned above was either explicitly created by the DBA, or was implicitly created by the FK.

Secondarily, if you don't need that INDEX otherwise, the existence of another INDEX slows down INSERTing -- because all indexes are updated on all INSERTs. (You probably _do_ need the index -- for JOINs.)

When SELECTing, the FKs have zero impact. JOINs are likely to use whatever INDEXes are appropriate, which might include the INDEX created by the FK.

To put performance in perspective, If you have thousands of rows, or even millions of rows in the tables, and if you are inserting only a few rows per second, the performance hit discussed here is usually insignificant. A billion-row table or INSERTing 1000 rows/sec -- that's a different matter.

> When I asked why, I was told that in order to improve performance all the foreign keys were removed.

Perhaps the advice came from a billion-row system, and perhaps it does not apply to your case.

Options: ReplyQuote


Subject
Written By
Posted
Re: Foreign Keys as to affect impact on performance
November 12, 2014 07:23PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.