Minimalist demo for unindexed foreign key Oracle Database Transactions and Locking Revealed by Thomas Kyte, Darl Kuhn https://books.google.com/books?id=zV8nCgAAQBAJ&pg=PA23 Session 1: create table p (x int primary key); create table c (x references p); --create index idx_c_x on c (x); insert into p values (1); insert into p values (2); commit; insert into c values (2); Session 2: delete from p where x=1; <-- this will hang unless idx_c_x exists Comment: The index idx_c_x helps by reducing the mode of TM lock on c requested by Session 2 from 4 (which cannot be got and Session 2 will wait) to 3 (which can be got). The index is useful in eliminating blocking even in cases it has low uniqueness; in that case it won't help with performance. ---------------------------------------------------------------------------------------------------- When does a foreign key not need an index? Tom Kyte says: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:292016138754 So, when do you NOT need to index a foriegn key. In general when the following conditions are met: o you do NOT delete from the parent table. (especially with delete cascade -- it is a double whammy) o you do NOT update the parent tables unique/primary key value. o you do NOT join from the PARENT to the CHILD (like DEPT->EMP). If you satisfy all three above, feel free to skip the index, it is not needed. If you do any of the above, be aware of the consequences.