Unindexed Foreign Keys In Oracle

Reading this excellent book on Oracle architecture – Expert One-On-One Oracle – By Thomas Kyte, I came through an interesting detail on deadlocks. The author mentions that based on his experience unindexed foreign keys are the number one causes of deadlocks.

There are two cases where Oracle will place a full table lock on a child table after modification of the parent table:

•          If I update the parent tableʹs primary key (a very rare occurrence if you follow the rules of relational databases that primary keys should be immutable), the child table will be locked in the absence of an index.
•          If I delete a parent table row, the entire child table will be locked (in the absence of an index) as well.

Deleting a parent table row 

Lets take an example of the second scenario which is more common. If I delete a row in table P, then the child table, C, will become locked ‐ preventing other updates against C from taking place for the duration of my transaction (assuming no one else was modifying C, of course; in which case my delete will wait). This is where the blocking and deadlock issues come in. By locking the entire table C, I have seriously decreased the  concurrency in my database ‐ no one will be able to modify anything in C. In addition, I have increased the probability of a deadlock, since I now ʹownʹ lots of data until I commit.

The probability that some other session will become blocked on C is now much higher; any session that tries to modify C will get blocked. Therefore, Iʹll start seeing lots of sessions that hold some pre‐existing locks getting blocked in the database. If any of these blocked sessions are, in fact, holding a lock that my session needs ‐ we will have a deadlock. The deadlock in this case is caused by my session obtaining many more locks then it ever needed.

Other Issues

By indexing the foreign key column, we can remove this locking issue all together. In addition to this table lock, an un‐indexed foreign key can also be problematic in the following cases:

• 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 run the following query, say to generate a report, youʹll find that not having the index in place will slow down the queries:

select * from dept, emp where emp.deptno = dept.deptno and dept.deptno = :X;

I don’t need an indexed foreign key

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)

If you satisfy all three above, feel free to skip the index ‐ it is not needed. If you do any of the above, be aware of the consequences. This is the one very rare time when Oracle tends to ʹover‐lockʹ data.

Advertisement