Do index type (unique and non-unique) affect table locking as result of DML?

Question

If there is an FK in child table that is not indexed then any updates to PK of the parent table locks entire child table, unless FK in child table is indexed.

My question is that if we want to prevent whole table lock then is the FK index has to be unique? Or It doesn't matter if the index is unique or non-unique?

Answer 1

The index doesn't have to be unique. It can be a regular or compound one.

The purpose of the index on a foreign key is to avoid full table locking while doing:

1. Delete in parent table
2. update of primary key column

and is useful while doing joins by avoiding Full Table scans.

See http://www.dba-oracle.com/t_foreign_key_indexing.htm