6

I have installed Oracle database 11g R2 with a customized application. In my environment we are experiencing locking on tables. I want to check if there are foreign keys in my database without the corresponding indexes. Is there any query or procedure to find un-indexed foreign keys?

2 Answers 2

7

Oracle SQL Developer ships with a report to find these unindexed columns.

View > Reports to access this feature

Here's the SQL behind this report:

select a.owner            "Owner",
                                 a.table_name       "Table_Name",
                                 a.constraint_name  "Constraint_Name",
                                 a.columns          "Foreign_Key_Column_1",
                                 b.columns          "Foreign_Key_Column_2",
                                 a.owner             sdev_link_owner,
                                 a.table_name        sdev_link_name,
                                 'TABLE'             sdev_link_type
                            from 
                            ( select a.owner, 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 sys.dba_cons_columns a,
                                     sys.dba_constraints b
                               where a.constraint_name = b.constraint_name
                                 and a.owner = b.owner
                                 and (:OWNER is null or instr(b.owner, upper(:OWNER))>0)
                                 and b.constraint_type = 'R'
                               group by a.owner, substr(a.table_name,1,30), substr(a.constraint_name,1,30) ) a, 
                            ( select table_owner, 
                                     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 sys.dba_ind_columns 
                               group by table_owner, substr(table_name,1,30), substr(index_name,1,30) ) b
                      where a.owner      = b.table_owner (+)
                        and a.table_name = b.table_name (+)
                        and substr(a.table_name,1,4) != 'BIN$'
                        and substr(a.table_name,1,3) != 'DR$'
                        and b.table_name is null
                        and b.columns (+) like a.columns || '%'
                      order by a.owner, a.table_name, a.constraint_name
3

There is no procedure exist in the database rather we can create one to list out all the un-indexed foreign key constraints. Actually if you google on it you will find plenty of scripts.
Well I have the following query to get the result.

SQL> select uc.table_name, uc.constraint_name
from user_constraints uc
where uc.constraint_type='R'
and exists
(select ucc.position, ucc.column_name
from user_cons_columns ucc
where ucc.constraint_name=uc.constraint_name
minus
select uic.column_position as position, uic.column_name
from user_ind_columns uic
where uic.table_name=uc.table_name
); 

TABLE_NAME             CONSTRAINT_NAME
------------------------------ ------------------------------
DEPARTMENTS            DEPT_MGR_FK
COUNTRIES              COUNTR_REG_FK
TB_COSTS               TB__COST_FK

SQL> 

(Where constraint_type='R' means we are interested in Referential (Foreign Key)).

1
  • 3
    +1. We have a nightly Oracle Scheduler job on all of our Oracle Databases that emails us the results from such a query in order to find new instances of those pesky unindexed foreign keys. Commented Mar 18, 2016 at 15:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.