Should Foreign Keys Be Indexed?-Indexes

The question of whether or not foreign keys should be indexed comes up frequently. We touched on this subject in Chapter 6 when discussing deadlocks. There, I pointed out that unindexed foreign keys are the biggest single cause of deadlocks that I encounter, due to the fact that an update to a parent table’s primary key or the removal of a parent record will place a table lock on the child table (no modifications to the child table will be allowed until the statement completes).

This locks many more rows than it should and decreases concurrency. I see it frequently when people are using a tool that ­generates the SQL to modify a table.

The tool generates an UPDATE statement that updates every column in the table, regardless of whether or not the value was modified. This, in effect, updates the primary key (even though they never changed the value).

For example, Oracle Forms will do this by default, unless you tell it to just send modified columns over to the database. In addition to the table lock issue that might hit you, an unindexed foreign key is bad in the following cases as well:

•\ When you have an ON DELETE CASCADE and have not indexed thechild table. For example, EMP is a child of DEPT. DELETE FROM DEPTWHERE DEPTNO = 10 should cascade to EMP. If DEPTNO in EMP is notindexed, you will get a full table scan of EMP. This full scan is probablyundesirable, and if you delete many rows from the parent table, thechild table will be scanned once for each parent row deleted.

•\ When you query from the parent to the child. Consider the EMP/DEPTexample again. It is very common to query the EMP table in thecontext of a DEPTNO. If you frequently query the following to generatea report or something, you’ll find not having the index in place willslow down the queries:

select *from dept, empwhere emp.deptno = dept.deptnoand dept.dname = :X;

This is the same argument I gave for indexing the NESTED_COLUMN_ID of a nested table in Chapter 10. The hidden NESTED_COLUMN_ID of a nested table is nothing more than a foreign key.

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.
•\ You do not update the parent table’s unique/primary key value,either purposely or by accident (via a tool).
•\ You do not join from the parent table to the child table, or moregenerally, the foreign key columns do not support an important access path to the child table, and you do not use them in predicates to select data from this table (such as DEPT to EMP).

If you satisfy all three criteria, feel free to skip the index—it is not needed and will slow down DML on the child table. If you do any of the three, be aware of the consequences.

As a side note, if you believe that a child table is getting locked via an unindexed foreign key and you would like to prove it (or just prevent it in general), you can issue the following:
ALTER TABLE DISABLE TABLE LOCK;

Now, any UPDATE or DELETE to the parent table that would cause the table lock will receive the following:
ERROR at line 1:
ORA-00069: cannot acquire lock — table locks disabled for

This is useful in tracking down the piece of code that is doing what you believe should not be done (no UPDATEs or DELETEs of the parent primary key), as the end users will immediately report this error back to you.