If you have been looking for a material proof showing Oracle using the foreign key index created on the child table while deleting from a parent table then here it is
drop table t2; drop table t1; create table t1 (col1 number primary key); create table t2 (col1 number primary key ,status varchar2(12) not null ,col2 number ,col2v number generated always as (case when status = 'ACTIVE' then col2 end) VIRTUAL ,constraint t2_fk foreign key (col2v) references t1(col1) ,constraint t2_ck check (status in ('ACTIVE','INACTIVE') and (status = 'INACTIVE' or col2 is not null)) ); create index t2_ind_fk on t2(col2v); insert into t1 select rownum from dual connect by level <=100; commit; insert into t2 (col1, status, col2) values (1, 'ACTIVE',50); alter session set skip_unusable_indexes = false; alter index t2_ind_fk unusable; -- implicit commit
I have created a pair of parent-child table (t1 and t2), an index on the foreign key on the t2 child table, set this index into an unusable state and changed the default skip_unusable_indexes parameter to false so that unusable indexes will not be skipped.
Now, I am going in the next PL/SQL anonymous block, to simulate a delete from a parent table using an autonomous transaction in order to mimic a different session (in fact a different transaction within the same session)
declare pragma autonomous_transaction; begin delete from t1 –- deleting from the parent table where col1 = 99; commit; end; / declare * ERROR at line 1: ORA-01502: index 'XXX.T2_IND_FK' or partition of such index is in unusable state ORA-06512: at line 4
See how deleting from the parent table (t1) triggered an error on the index of the foreign key constraint created on the child table (t2). This is a simple way to show the mechanism used by Oracle in order to avoid a child table lock (before eventually a deadlock situation) simply by using the index on the foreign key.