Brief Description:
After reading “Expert Indexing in Oracle Database 11g ” book, I decided to share the following information with you. Which, I think, is very very useful.
It is better to index foreign key columns to avoid locking issues. Note, that locking issues are avoided when index type is B-tree, not Bitmap.
The Scenario:
Note: This is the quote from the following book:
Expert Indexing in Oracle Database 11g
Maximum Performance for Your Database
Darl Kuhn
Sam R. Alapati
Bill Padfield
“Here’s a simple example that demonstrates the locking issue when foreign key columns are not indexed.
First, create two tables (DEPT and EMP) and associate them with a foreign key constraint.
create table emp(emp_id number primary key, dept_id number);
create table dept(dept_id number primary key);
alter table emp add constraint emp_fk1 foreign key (dept_id) references dept(dept_id);
Now insert some data.
insert into dept values(10);
insert into dept values(20);
insert into dept values(30);
insert into emp values(1,10);
insert into emp values(2,20);
insert into emp values(3,10);
commit;
Open two terminal sessions. From one, delete one record from the child table (don’t commit).
delete from emp where dept_id = 10;
Now attempt to delete from the parent table some data not impacted by the child table delete.
delete from dept where dept_id = 30;
The delete from the parent table hangs until the child table transaction is committed. Without a regular B-tree index on the foreign key column in the child table, any time you attempt to insert or delete in the child table, it places a table-wide lock on the parent table, which prevents deletes or updates in the parent table until the child table transaction completes.
Now run the prior experiment, except this time additionally create an index on the foreign key column of
the child table.
create index emp_fk1 on emp(dept_id);
You should be able to independently run the prior two delete statements. When you have a B-tree index on
the foreign key columns, if deleting from the child table, Oracle will not excessively lock all rows in the
parent table.”
DBA Knowledge Base 