Re: foreign key: auto-index creation?

From: Graham Thornton <graham.thornton_at_ln.ssw.abbott.com>
Date: 2000/07/24
Message-ID: <397C4662.399184F@ln.ssw.abbott.com>#1/1

No,

Oracle does not automatically generate an index for your foreign key.

But you can use the following script to discover which foreign keys are not supported by an index:

rem ** Script to find missing foreign key indeces - author unknown

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 substr(a.table_name,1,30) table_name,

   substr(a.constraint_name,1,30) 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)) ||
      max(decode(position,11,', '||substr(column_name,1,30),NULL)) ||
      max(decode(position,12,', '||substr(column_name,1,30),NULL)) ||
      max(decode(position,13,', '||substr(column_name,1,30),NULL)) ||
      max(decode(position,14,', '||substr(column_name,1,30),NULL)) ||
      max(decode(position,15,', '||substr(column_name,1,30),NULL)) ||
      max(decode(position,16,', '||substr(column_name,1,30),NULL)) columns

    from user_cons_columns a, user_constraints b    where a.constraint_name = b.constraint_name      and b.constraint_type = 'R'
   group by substr(a.table_name,1,30), substr(a.constraint_name,1,30) ) a, ( select substr(table_name,1,30) table_name, substr(index_name,1,30) index_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)) ||

      max(decode(column_position,11,', '||substr(column_name,1,30),NULL)) ||

      max(decode(column_position,12,', '||substr(column_name,1,30),NULL)) ||

      max(decode(column_position,13,', '||substr(column_name,1,30),NULL)) ||

      max(decode(column_position,14,', '||substr(column_name,1,30),NULL)) ||

      max(decode(column_position,15,', '||substr(column_name,1,30),NULL)) ||

      max(decode(column_position,16,', '||substr(column_name,1,30),NULL))

columns

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

Hope that helps

Graham

Randall Parker wrote:

> I've been told that Sybase ASA automatically creates an index in the
> referring table for all declared foreign key constraints. Does Oracle do
> likewise?
>
> DB2 and MS SQL Server both require explicit creation of indexes to
> support foreign keys. I'm wondering if Oracle is more like them.
Received on Mon Jul 24 2000 - 00:00:00 CDT