This is one of the mantras of Tom Kyte (see this post on his site).
Here's a resume why:
First, table-level locks can occur when the primary key of the parent table is updated.
In addition to the table lock issue, an unindexed foreign key is bad in the following cases as well:
- When you have an on delete cascade and have not indexed the child table. For example EMP is child of DEPT. Delete deptno = 10 should cascade to EMP. If deptno in emp is not indexed, you will get a full table scan of EMP. This full scan is probably undesirable and if you delete many rows from the parent table, the child table will be scanned once for each parent row deleted.
- When you query from the PARENT to the CHILD.
Consider the EMP, DEPT example again. It is very common to query the EMP table in the context of a deptno. If you frequently query:
select * from dept, emp
where emp.deptno = dept.deptno and dept.deptno = :X;
not having the index in place will slow down the queries.
So, when do you NOT need to index a foreign key? In general when the following conditions are met:
- you do NOT delete from the parent table. (especially with delete cascade)
- you do NOT update the parent tables unique/primary key value.
- you do NOT join from the PARENT to the CHILD (like DEPT->EMP).
