Problem solve Get help with specific problems with your technologies, process and projects.

Identify missing FK indexes and create indexes to avoid deadlocks

A script to identify missing foreign key indexes and automatically create appropriate ones.

Have you ever wondered how to identify the missing indexes on foreign keys and create the appropriate indexes automatically? Missing indexes on FKs are the main cause of deadlocks. This script will help you do this. Note: the script assumes that any index in a particular schema has a maximum of 10 columns. If you have more columns, change the script accordingly.

select	'CREATE INDEX ' || owner || '.' || replace(CONSTRAINT_NAME,'FK_','IX_') || 
	' ON ' || owner || '.' || table_name || ' (' || col_list ||') TABLESPACE &ts;' Indx from 
 	(select cc.owner, cc.TABLE_NAME, cc.CONSTRAINT_NAME,
           max(decode(position, 1,     '"' ||
                  substr(column_name,1,30) ||'"',NULL)) ||
           max(decode(position, 2,', '||'"'||
                  substr(column_name,1,30) ||'"',NULL)) ||
           max(decode(position, 3,', '||'"'||
                  substr(column_name,1,30) ||'"',NULL)) ||
           max(decode(position, 4,', '||'"'||
                  substr(column_name,1,30) ||'"',NULL)) ||
           max(decode(position, 5,', '||'"'||
                  substr(column_name,1,30) ||'"',NULL)) ||
           max(decode(position, 6,', '||'"'||
                  substr(column_name,1,30) ||'"',NULL)) ||
           max(decode(position, 7,', '||'"'||
                  substr(column_name,1,30) ||'"',NULL)) ||
           max(decode(position, 8,', '||'"'||
                  substr(column_name,1,30) ||'"',NULL)) ||
           max(decode(position, 9,', '||'"'||
                  substr(column_name,1,30) ||'"',NULL)) ||
           max(decode(position, 10,', '||'"'||
                  substr(column_name,1,30) ||'"',NULL)) col_list
           from dba_constraints dc, dba_cons_columns cc
           where dc.owner = cc.owner
           and dc.CONSTRAINT_type = 'R'
           and upper(dc.owner) = upper('&sch_owner')
           group by cc.owner, cc.TABLE_NAME, cc.CONSTRAINT_NAME 
 	) con
  	where not exists (
		select 1 from
          		( select table_owner, table_name,   
           			max(decode(column_position, 1,     '"'||
                  			substr(column_name,1,30)  ||'"',NULL)) ||
           			max(decode(column_position, 2,', '||'"'||
                  			substr(column_name,1,30)  ||'"',NULL)) ||
           			max(decode(column_position, 3,', '||'"'||
                  			substr(column_name,1,30)  ||'"',NULL)) ||
           			max(decode(column_position, 4,', '||'"'||
                  			substr(column_name,1,30)  ||'"',NULL)) ||
           			max(decode(column_position, 5,', '||'"'||
                  			substr(column_name,1,30)  ||'"',NULL)) ||
           			max(decode(column_position, 6,', '||'"'||
                  			substr(column_name,1,30)  ||'"',NULL)) ||
           			max(decode(column_position, 7,', '||'"'||
                  			substr(column_name,1,30)  ||'"',NULL)) ||
           			max(decode(column_position, 8,', '||'"'||
                  			substr(column_name,1,30)  ||'"',NULL)) ||
           			max(decode(column_position, 9,', '||'"'||
                  			substr(column_name,1,30)  ||'"',NULL)) ||
           			max(decode(column_position, 10,', '||'"'||
                  			substr(column_name,1,30)  ||'"',NULL)) col_list
           			from dba_ind_columns 
           		where upper(table_owner) = upper('&sch_owner')
           		group by table_owner, table_name, index_name ) col
	where con.owner = col.table_owner 
	and con.table_name = col.table_name  
	and con.col_list = substr(col.col_list, 1, length(con.col_list) ) ) ;  

set verify on
set pages 60

Reader Feedback

Predrag R. writes: This solution will also attempt to create indexes for cases where the FK column list is already indexed, but the order of index columns does not match that of FK columns. For example: FK column list (A,B,C) is already indexed as (A,C,B). For the purpose of preventing (dead)locks we do not need to create index on (A,B,C).

Geoff H. writes: "I do hope the author knows that 'deadlock' refers to response time and not 'Deadlocking' as in tables locked in conflicting commit paths in a DML statement, which is due to poor locking strategy and ultimately application design. If this is not table locking or response time please explain your point further.

Prince Kumar responds: If there is no index on the FK and there is high insert/update activity it can cause deadlocks. Also, queries from parent to child without the index have to do a FTS on the child table. Predrag is correct in saying that if there is already an index on the FK with all the columns but in a different order, the script will not catch them. The following should cover why an index is needed on FK columns in most cases. If you see a lot of locks on SSX/SX modes and dead locks, it's mostly due to the missing indexes on FKs.

Check out the "Concurrency Control, Indexes, and Foreign Keys" section in this article and the "No Index on the Foreign Key" section in this article on Technet. Also, plese read Doc ID 11828.1 on Metalink--"FOREIGN KEYS, INDEXES AND PARENT TABLE LOCKING"--for the reasoning. Here is the link.

For More Information

  • What do you think about this tip? E-mail the Editor at [email protected] with your feedback.
  • The Best Oracle Web Links: tips, tutorials, scripts, and more.
  • Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
  • Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
  • Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.

Dig Deeper on Oracle and SQL