How are foreign key indexes helpful?

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

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.

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.

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).

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.