Re: Foreign Keys and Indexes

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 1997/01/28
Message-ID: <32ed5f42.3967414@nntp.mediasoft.net>#1/1

On Mon, 27 Jan 1997 15:41:28 -0600, lolin_at_unexmail.ucdavis.edu wrote:

>I was surprised to find that when I created a foreign key on a field in a
>child table that access to that table through the key hadn't improved.
>
>Do I have to also create an index on the same field? Why can't Oracle
>use the implied index created for the foreign key? Seems like a waste to
>have to create a "second" index on the same
>field.
>-------------------==== Posted via Deja News ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet

A foreign key requires no index. To create a foreign from from EMP(deptno) to DEPT(deptno) does not imply that you need an index on the EMP table (only a UNIQUE or PRIMARY KEY constraint on the dept(deptno) field).

There are performance increases to be found from indexing foreign keys (especially when going from Parent to Child, eg: select * from emp, dept where dept.deptno = 10 and emp.deptno = dept.deptno, would be nice to have an index on EMP(deptno)).....

also, there are locking issues with respect to unindexed foreign keys. See the server concepts manual for more info.

to find unindexed foreign keys you can use the following query in sql*plus:

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
Oracle Government
tkyte_at_us.oracle.com                          

http://govt.us.oracle.com

• Check out Oracle Governments web site! ----- Follow the link to "Tech Center" and then downloadable Utilities for some free software...

  ---

statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Tue Jan 28 1997 - 00:00:00 CST