Oracle Foreign Key without Index Test

We've been having some Oracle deadlock issues that have been hard to reproduce locally. After a lot of investigation and solving of important problems that happened not to be THE problem we figured out that while we've been pretty good creating integrity constraints in the database we have not been very good about making sure that every foreign key has a corresponding index. And that can lead to problems.

So we had a situation were our documents table had a foreign key on the accounts table that was not indexed. So updating an account row lead to a whole table lock on documents (instead of just a row lock which would have happened if there was an index) and that was very bad when we had two separate processes where one was doing a bunch of accounts stuff and the other was doing a lot of documents stuff. Deadlocks for everyone!

The sad thing is that if we had just drank the Rails cool-aid about having no integrity constraints in the db we would have been fine but we got into trouble by only implementing constraints and not the indexes they work much better with.

So we found this cool bit of SQL that helped us find all the foreign keys missing an index here:

And that help us solve the deadlock problem. But what about the future? If only there was a way to run this check periodically and automatically... Time for a test!

As an additional bonus, the failing output of the test tells you how to write the index you need. Big thanks to Dave Bortz for tracking down this problem -- I just swooped in and wrote the test. And took credit in this here blog.


Dean R said…
Wow, I should have read this before running my mouth off today !

One minor point - the indexed case in oracle still acquires a table lock on the child (referring) table - it's just that its a shared lock in the case of the indexed FK, and an shared row exclusive one in the non-indexed case. Who'd have thunk !
Teracomp DBA said…
Hello Jake

Very good post and script.

I did some improvements in this scripts to check for this issue in all database schemas.

set line 1000
set head off
set pagesize 0

select 'CREATE INDEX '||owner||'.'||substr(constraint_name,1,28)||'_I ON '||owner||'.'||table_name||'('||
cname1 || nvl2(cname2,','||cname2,null) ||
nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) ||
nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) ||
nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null)||');'
from (select b.owner,
max(decode( position, 1, column_name, null )) cname1,
max(decode( position, 2, column_name, null )) cname2,
max(decode( position, 3, column_name, null )) cname3,
max(decode( position, 4, column_name, null )) cname4,
max(decode( position, 5, column_name, null )) cname5,
max(decode( position, 6, column_name, null )) cname6,
max(decode( position, 7, column_name, null )) cname7,
max(decode( position, 8, column_name, null )) cname8,
count(*) col_cnt
from (select owner,
substr(table_name,1,30) table_name,
substr(constraint_name,1,30) constraint_name,
substr(column_name,1,30) column_name,
from dba_cons_columns ) a,
dba_constraints b
where a.owner = b.owner
and a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
group by b.owner, b.table_name, b.constraint_name
) cons
where col_cnt > ALL
( select count(*)
from dba_ind_columns i
where i.index_owner = cons.owner
and i.table_name = cons.table_name
and i.column_name in (cname1, cname2, cname3, cname4, cname5, cname6, cname7, cname8 )
and i.column_position <= cons.col_cnt
group by i.index_name
order by owner, table_name;

set head on
set pagesize 10

I hope that helps!

My best regards,

Popular posts from this blog

SICP Wasn’t Written for You

What's a Good Flog Score?

Point Inside a Polygon in Ruby