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