Un-Indexed Foreign key

The number one cause of deadlocks in the Oracle database, in my experience, is
unindexed foreign keys. There are two cases where Oracle will place a full table lock on a
child table after modification of the parent table:

• If I update the parent table's primary key (a very rare occurrence if you follow the rules of relational databases that primary keys should be immutable), the child table will be locked in the absence of an index. 
• If I delete a parent table row, the entire child table will be locked (in the absence of an index) as well

drop table t1 purge;
drop table t2 purge;

create table t1(x int primary key);
create table t2(y int references t1);

Scenario - I
                         Session 1

 

                     Session 2

 

                   Session 1 (gets blocked because of Un - Indexed Foreign keys)
 

  

Scenairo - II 

                    

drop table t2 purge;
drop table t1 purge;

create table t1(x int primary key);
create table t2(y int references t1);
create index t2_ind on t2(y);

                Session 1

  

              Session 2

  

              Session 1  (No Blocking now, since Foreign keys is Indexed).

 

So, when do you not need to index a foreign key? The answer is, in general, when the following conditions are met:

• You do not delete from the parent table.
•  
• You do not update the parent table's unique/primary key value
• You do not join from the parent to the child (like DEPT to EMP)

If you satisfy all three above, feel free to skip the index - it is not needed.

So what kind of Index should be created on Foreign keys?

    Any index that contains the foreign key on the leading edge will do

-- eg:  create table emp ( empno int primary key, ...  deptno references DEPT );

•     create index on dept(deptno,empno);  -- this is OK
•     create index on dept(empno,deptno);  -- this will not prevent the fkey lock
•     create index on dept(deptno);  -- this is OK