Skip to content

Oracle and Foreign Keys without Index

The problem with foreign key constraints lacking a supporting index in Oracle is not a new one. Updating a primary key (you shouldn't do that anyway) or deleting a row in the parent table needs to make sure that no entry referencing the old row is created in the child table until the transaction commits. This blog entry is about a change in Oracle 11g I stumbled upon and my script to locate foreign key constraints without an supporting index.

Unless there is an index on the foreign key constraint columns Oracle will use TM enqueues to establish a lock in the session that performs the change. These locks reduce the scalability as they lock the whole table and therefore prevent all other sessions from changing unrelated records. Oracle held the lock for the duration of the whole transaction in older releases. But this has improved greatly with Oracle 10g and the lock is enforced for a much smaller time now.

Oracle 11g (tested on 11.2) introduced another change to the locking code. In our test for the migration to 11g we had frequent application freezes. Analysis revealed that there seems to be a change in the lock modes. This leads to heavier TM enqueue contention and caused a freeze when sessions did not commit for a longer period of time. A test case showed that a missing foreign key index on the child table caused the behaviour and creating the index solved the issue instantly. Luckily we found the cause before the application went live on 11g.

Oracle index guru Richard Foote has a note on the locking modes when policing FK constraints where all the internals are explained.

The issue made me rework my script to locate missing foreign key indexes. There are a couple of scripts already available on other sites. But most of the time these scripts either check only the current schema or they expect the index and the constraint to have the exact same columns in the exact same order. But a test easily shows that the index may in fact have trailing columns and the constraint columns may appear in any order as long as they are the leading columns in the index.

So here is my script fkeyidx.sql to check for missing foreign key indexes in Oracle. It uses listagg() and therefore needs Oracle 11g.

Trackbacks on : PingBack

Unfortunately, the contents of this trackback can not be displayed.


Display comments as Linear | Threaded

Stefan on :

I just uploaded a new version of the script. Unfortunately the old script had problems identifying the correct columns when the column name of the foreign key was different from the column name of the referenced (primary) key. This should be fixed in the new version of the script.

Thanks to Gerrit for bringing the problem to my attention.


Add Comment

Standard emoticons like :-) and ;-) are converted to images.
Textile-formatting allowed
E-Mail addresses will not be displayed and will only be used for E-Mail notifications.

To prevent automated Bots from commentspamming, please enter the string you see in the image below in the appropriate input box. Your comment will only be submitted if the strings match. Please ensure that your browser supports and accepts cookies, or your comment cannot be verified correctly.

Form options

Submitted comments will be subject to moderation before being displayed.