Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: Enterprise
Expertise: Beginner
May 4, 2000

Indexing Foreign Key Columns in Child Tables

Although an index is not mandatory on your foreign keys, if they are not indexed you will cause additional locking when certain Data Manipulation Language statements are executed against the parent table.

Without the index, checking for existing child records when attempting a delete of a master record (cascade or restrict, makes no difference) incurs a full table scan on the child table. Thus, the transaction on the parent table acquires a "share" lock on the child table, thereby, blocking all other transactions from performing DML on the child table for the duration of the transaction.

Unless your parent table is static, you should maintain an index on the child table's foreign key column(s) if you wish to avoid performance problems that can occur when the parent table is being modified concurrently with other transactions that modify the parent or child table.

Jayanta Sengupta
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap