Re: Index on Foreign key necessary to prevent lock problems ?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 29 May 1998 13:41:37 GMT
Message-ID: <3571b98f.4926764@192.86.155.100>

A copy of this was sent to philippe <philippe_at_arpege.imaginet.fr> (if that email address didn't require changing) On Fri, 29 May 1998 12:28:41 +0200, you wrote:

>I read that it might be necessary to put an index on an Foreign key to
>prevent lock problems on the master table.
>
>Any Idea

Chapter 6 in v7.x docs Chapter 9 i v8.x docs, Application Developers Guide section on Concurrency Control, Indexes, and Foreign Keys explains why a table lock on a child table will take place when you

• delete from the parent table
• update the parents KEY

if you have an unindexed foreign key.

Note however that if you don't have an index on the foreign key in the child table and you delete from the parent table -- you will FULL SCAN the child table (not desirable in most cases). So if you delete from the parent and want the delete to happen quickly, you will index the childs foreign key. A similar argument applies for updates.

In 7.0 and 7.1 the locking was done differently. Same chapter for those databases describe that scenario (the parent table would get locked instead of the child and for different dml commands).

In case you want to see how many foreign keys you have that are currently not indexed, here is a script that will do it:

---

column columns format a20 word_wrapped
column table_name format a30 word_wrapped

select decode( b.table_name, NULL, '****', 'ok' ) Status,

           a.table_name, a.columns, b.columns
from
( select a.table_name, a.constraint_name,

	     max(decode(position, 1,     column_name,NULL)) || 
	     max(decode(position, 2,', '||column_name,NULL)) || 
	     max(decode(position, 3,', '||column_name,NULL)) || 
	     max(decode(position, 4,', '||column_name,NULL)) || 
	     max(decode(position, 5,', '||column_name,NULL)) || 
	     max(decode(position, 6,', '||column_name,NULL)) || 
	     max(decode(position, 7,', '||column_name,NULL)) || 
	     max(decode(position, 8,', '||column_name,NULL)) || 
	     max(decode(position, 9,', '||column_name,NULL)) || 
	     max(decode(position,10,', '||column_name,NULL)) || 
	     max(decode(position,11,', '||column_name,NULL)) || 
	     max(decode(position,12,', '||column_name,NULL)) || 
	     max(decode(position,13,', '||column_name,NULL)) || 
	     max(decode(position,14,', '||column_name,NULL)) || 
	     max(decode(position,15,', '||column_name,NULL)) || 
	     max(decode(position,16,', '||column_name,NULL)) columns

    from user_cons_columns a, user_constraints b    where a.constraint_name = b.constraint_name      and b.constraint_type = 'R'
   group by a.table_name, a.constraint_name ) a, ( select table_name, index_name,

	     max(decode(column_position, 1,     column_name,NULL)) || 
	     max(decode(column_position, 2,', '||column_name,NULL)) || 
	     max(decode(column_position, 3,', '||column_name,NULL)) || 
	     max(decode(column_position, 4,', '||column_name,NULL)) || 
	     max(decode(column_position, 5,', '||column_name,NULL)) || 
	     max(decode(column_position, 6,', '||column_name,NULL)) || 
	     max(decode(column_position, 7,', '||column_name,NULL)) || 
	     max(decode(column_position, 8,', '||column_name,NULL)) || 
	     max(decode(column_position, 9,', '||column_name,NULL)) || 
	     max(decode(column_position,10,', '||column_name,NULL)) || 
	     max(decode(column_position,11,', '||column_name,NULL)) || 
	     max(decode(column_position,12,', '||column_name,NULL)) || 
	     max(decode(column_position,13,', '||column_name,NULL)) || 
	     max(decode(column_position,14,', '||column_name,NULL)) || 
	     max(decode(column_position,15,', '||column_name,NULL)) || 
	     max(decode(column_position,16,', '||column_name,NULL)) columns

    from user_ind_columns
   group by table_name, index_name ) b
where a.table_name = b.table_name (+)
  and b.columns (+) like a.columns || '%' /  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri May 29 1998 - 08:41:37 CDT