Having Unindexed foreign keys can be a performance issue. There are two issues associated with unindexed foreign keys. The first is the fact that a table lock will result if you update the parent records primary key (very very unusual) or if you delete the parent record and the child's foreign key is not indexed.

To read about this issue, please see the Application Developers Guide the section on Maintaining Data Integrity/Concurrency Control, Indexes, and Foreign Keys. If you have an account on technet.oracle.com (they are free, get one) you can click here to read about it.

The second issue has to do with performance in general of a parent child relationship. Consider that if you have an on delete cascade and have not indexed the child table (eg: EMP is child of DEPT. Delete deptno = 10 should cascade to EMP. If deptno in emp is not indexed -- full table scan). This full scan is probably undesirable and if you delete many rows from the parent table, the child table will be scanned once for each parent row deleted.

Also consider that for most (not all, most) parent child relationships, we query the objects from the 'master' table to the 'detail' table. The glaring exception to this is a code table (short code to long description). For master/detail relationships, if you do not index the foreign key, a full scan of the child table will result.

So, how do you easily discover if you have unindexed foreign keys in your schema? This script can help. When you run it, it will generate a report such as:

SQL> @unindex

STAT TABLE_NAME                     COLUMNS              COLUMNS
---- ------------------------------ -------------------- --------------------
**** APPLICATION_INSTANCES          AI_APP_CODE
ok   EMP                            DEPTNO               DEPTNO

The **** in the first row shows me that I have an unindexed foreign key in the table APPLICATION_INSTANCES. The ok in the second row shows me I have a table EMP with an indexed foreign key.