Databases Reference
In-Depth Information
This query will prompt you for a schema name and then will display foreign key constraints that
don't have corresponding indexes. This query also checks for the index type; bitmap indexes may exist
on foreign key columns but don't prevent locking issues.
Table Locks and Foreign Keys
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.
Summary
B-tree indexes are the default index type used in Oracle databases. For most applications, B-tree indexes
are sufficient. With high cardinality columns, a B-tree index will usually provide considerable
performance benefits.
