Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

How are foreign key indexes helpful? What is the criteria for keeping a foreign key index?

share|improve this question
1  
You're asking too many unrelated questions across a variety of topics. Please review the FAQ for tips on how to ask a good question and consider either editing this question to narrow its scope or delete and ask more focused, separate questions. –  Mark Storey-Smith Feb 2 '12 at 12:08
1  
@MarkStorey-Smith I understand the points you noted. I'm now editing my question to be specific. –  stacked Feb 2 '12 at 12:51
1  
You might want to read this: tkyte.blogspot.com/2009/10/httpasktomoraclecomtkyteunindex.html –  a_horse_with_no_name Feb 2 '12 at 14:34

4 Answers 4

In addition to JOINs another benefit of indexing Foreign Key columns is that it can speed up enforcement of the Foreign Key constraint for some DML operations.

If you delete a row from an Orders table then the RDBMS would need to ensure that this would not leave an orphaned row in OrderDetails. Obviously this is easier if it can be verified with the use of an index.

share|improve this answer
1  
+1 - good point, and a lot of times this cost isn't taken into account. –  JNK Feb 2 '12 at 15:14

This will be non-specific to oracle, but in general foreign key indexes are most useful because they speed up JOINs.

If you have a foreign key field, it is extremely likely you will use it as a JOIN key as well - that's the whole point of an FK, to make a direct association between one field in tablea to another field in tableb.

Having this field indexed will speed up any queries that use that field in either the WHERE filter or in the JOIN criteria.

share|improve this answer

In general you should index all foreign keys because they help reduce locking during updates or deletes on the parent table. This is especially true if you are still running Oracle 10.x, with 11.x this has become a bit better.

In a nutshell, if you are missing an index on the foreign key and do an UPDATE to the PK of the parent table or DELETE rows in the parent table, Oracle will need to do a full table scan on the child table in order to ensure that the PK is not used there.

From the Concepts manual:

If foreign keys are not indexed, then the child table will probably be locked more frequently, deadlocks will occur, and concurrency will be decreased. For this reason foreign keys should almost always be indexed. The only exception is when the matching unique or primary key is never updated or deleted.

Of course the index will not only prevent that lock it will also speed up that check as Martin Smith has already said.

As a final note, the following quote from Tom Kyte might be helpful:

So, when do you not need to index a foreign key? The answer is, in general, when the following conditions are met:

  • You do not delete from the parent table.
  • You do not update the parent table’s unique/primary key value (watch for unintended updates to the primary key by tools!).
  • You do not join from the parent to the child (like DEPT to EMP).
share|improve this answer

The criteria should be same as for any other index i.e. performance and concurrency considerations. If the index is not highly selective, for example, you should consider dropping it.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.