Unindexed Foreign Keys

A performance issue very common in Oracle, is the FK constrainst that are not indexed, this means that under certain conditions and especially when working with tables large or very large, performance plummets. Oracle recommends that as a rule Foreign Keys all have their associated index. However, one thing is theory and another practice, sometimes we can not have all FK indexed, from problems of physical space (of course disk is more cheaper every day) up to has performance problem. I have worked with tables that had the order of hundreds of indexes, the problem comes when do a simple insert will be 2 msg (only the data in the block in memory) and update all its indexes take a minute.

Here I put a little script to identify all FKs that are not indexed.

 

 
SELECT  
  FK.TABLE_NAME, 
  FK.CONSTRAINT_NAME
FROM    
  DBA_CONSTRAINTS FK
WHERE   
  FK.CONSTRAINT_TYPE = 'R' AND     
  EXISTS (   
    SELECT  
      FC.POSITION, 
      FC.COLUMN_NAME
    FROM    
      DBA_CONS_COLUMNS FC
    WHERE   
       FC.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
    MINUS
    SELECT  
       IC.COLUMN_POSITION, 
       IC.COLUMN_NAME
    FROM    
       DBA_IND_COLUMNS IC
    WHERE   
       IC.TABLE_NAME = FK.TABLE_NAME
  )  /*** END OF EXISTS ***/  
/

 
HTH – Antonio NAVARRO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s