Re: Foreign keys w/out indexes

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/12/18
Message-ID: <349952af.36004341@inet16>#1/1

On 17 Dec 1997 19:01:26 GMT, "Beth Powers" <bpowers_at_fiore-ind.com> wrote:

>what is the impact on the database if there are quite a few (like 40-50)
>foreign keys that do not have indexes?

It depends on the version (7.0 and 7.1 locked unindexed foreign keys one way, 7.2 on up does it differently). See the Chapter 6 of the server Application Developers Guide, the section on "Concurrency Control, Indexes, and Foreign Keys" for info on the locking consequences.

As for query performance, IMO it is adversely impacted in general by not have a foreign key indexed. If I have a parent table and a child table, I *typically* query the parent table picking up all of the children records. I access the child by the foreign key. If that is not indexed -- full scan. The main exception to this is if the parent table is really a code table (eg: 2 columns, a short code to a longer description). Then I use child table to access the parent table. In this case, you need to understand the locking implications from above and see if that is all right in your environment.

If you are curious as to which objects in your schema have unindexed foreign keys, you can use the following script to find them:

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
Bethesda MD  

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 Thu Dec 18 1997 - 00:00:00 CST