Don't Let Referential Integrity Degrade Your Database Performance

         It depicts that, if there is no index created for the foreign key constraints related columns, there may be big performance problem. The major reasons are:

         1) FK constraint represents a relation between tables, and if these tables are part of a SQL query, there's a 99.9 percent chance that they will be joined on the constraint column. In this case you definitely need an index that supports SQL queries.”

         2) Another reason to have indexes for FK constraints is to avoid locking conflicts during an update of the child table. Oracle provides a row-level locking mechanism that removes many locking problems contained in databases with page-level and block-level locking. However, even row-level locking won't help if frequent updates to the child table cause it to lock the entire parent table just because the supporting index is missing.

         3) There is also a less-obvious problem that occurs with massive DELETE operations on the parent table. This is dangerous because, unlike the SELECT command, it triggers SQL queries implicitly in the background and you could spend hours and days trying to figure out why your simple DELETE command is unable to complete.

         You can find the details at http://www.devx.com/gethelpon/10MinuteSolution/16595/0/page/1.

转载于:https://www.cnblogs.com/PeterWang/archive/2005/07/18/195300.html