Skip to content

performance problem with unindexed foreign keys – reference article for the new oracle dba





this article tells the oracle dba about performance problems if foreign keys on tables are not indexed..

Unindexed Foreign Keys

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.

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 || '%'
/

 

 

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.

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 dba_cons_columns a, dba_constraints b
   where a.constraint_name = b.constraint_name
     and b.constraint_type = 'R' and a.owner= 'LL_PROD'
   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 dba_ind_columns 
 where index_owner= 'LL_PROD'
   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 || '%'
/

 

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Post a Comment

You must be logged in to post a comment.