Un-indexed foreign keys - Part III

If we want to index our foreign key columns, what is the best index to build against them?

I mean does the index need to be separate and special on that particular column to be effective?

Or is it better to index the column as part of a larger indexing strategy

 

rajesh@ORA11G> set feedback off

rajesh@ORA11G> create table p(x int primary key, y int);

rajesh@ORA11G> create table c(x references p,y int);

rajesh@ORA11G> create index c_idx on c(x);   <<=== this is OK, will prevent lock

rajesh@ORA11G> insert into p values(1,null);

rajesh@ORA11G> insert into p values(2,null);

rajesh@ORA11G> commit;

rajesh@ORA11G>

rajesh@ORA11G> set feedback on

rajesh@ORA11G> insert into c values(1,null);

 

1 row created.

 

rajesh@ORA11G> declare

  2     pragma autonomous_transaction;

  3  begin

  4     delete from p where x = 2;

  5     rollback;

  6  end;

  7  /

 

PL/SQL procedure successfully completed.

 

rajesh@ORA11G> rollback;

 

Rollback complete.

 

rajesh@ORA11G>

rajesh@ORA11G>

 

 

rajesh@ORA11G>

rajesh@ORA11G> set feedback off

rajesh@ORA11G> drop index c_idx;

rajesh@ORA11G> create index c_idx on c(x,y); <<=== this is OK, will prevent lock

rajesh@ORA11G> set feedback on

rajesh@ORA11G>

rajesh@ORA11G> insert into c values(1,null);

 

1 row created.

 

rajesh@ORA11G>

rajesh@ORA11G> declare

  2     pragma autonomous_transaction;

  3  begin

  4     delete from p where x = 2;

  5     rollback;

  6  end;

  7  /

 

PL/SQL procedure successfully completed.

 

rajesh@ORA11G> rollback;

 

Rollback complete.

 

rajesh@ORA11G>

 

rajesh@ORA11G> set feedback off

rajesh@ORA11G> drop index c_idx;

rajesh@ORA11G> create index c_idx on c(y,x); <<=== this will not prevent fk lock

rajesh@ORA11G> set feedback on

rajesh@ORA11G>

rajesh@ORA11G> insert into c values(1,null);

 

1 row created.

 

rajesh@ORA11G>

rajesh@ORA11G> declare

  2     pragma autonomous_transaction;

  3  begin

  4     delete from p where x = 2;

  5     rollback;

  6  end;

  7  /

declare

*

ERROR at line 1:

ORA-00060: deadlock detected while waiting for resource

ORA-06512: at line 4

 

 

rajesh@ORA11G> rollback;

 

Rollback complete.

 

rajesh@ORA11G>

 

rajesh@ORA11G>

rajesh@ORA11G> set feedback off

rajesh@ORA11G> drop index c_idx;

rajesh@ORA11G> create index c_idx on c(x,y) INVISIBLE;<= this is OK, will prevent lock

rajesh@ORA11G> set feedback on

rajesh@ORA11G>

rajesh@ORA11G> insert into c values(1,null);

 

1 row created.

 

rajesh@ORA11G>

rajesh@ORA11G> declare

  2     pragma autonomous_transaction;

  3  begin

  4     delete from p where x = 2;

  5     rollback;

  6  end;

  7  /

 

PL/SQL procedure successfully completed.

 

rajesh@ORA11G> rollback;

 

Rollback complete.

 

rajesh@ORA11G>

 

Any index that contains the foreign key on the leading edge will prevent full table lock on child table.