How are foreign key indexes helpful? What is the criteria for keeping a foreign key index?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
This will be non-specific to oracle, but in general foreign key indexes are most useful because they speed up
If you have a foreign key field, it is extremely likely you will use it as a
Having this field indexed will speed up any queries that use that field in either the
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
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:
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:
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.