oracle外键如何创建索引,子表建立外键的索引问题

本帖最后由 Yong Huang 于 2013-9-30 09:31 编辑

An index on the child table foreign key column is to prevent locking the parent table. To quote "Pro Oracle Database 12c Administration" by Darl Khun:

"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, a table-wide lock is placed on the parent table; this prevents deletes or updates in the parent table until the child table transaction completes."

There's no requirement on whether the index should be only on the column(s) on which the foreign key is defined. Here's a test on Oracle 11.2.0.4 (code mostly from the quoted book):

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);

insert into dept values(10);insert into dept values(20);

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;

Session 1: delete from emp where dept_id= 10;

Session 2: delete from dept where dept_id= 30;

It hangs.

Go back to Session 1 and rollback, and to Session 2 (where the delete is done now) and rollback. Then create this index:

create index emp_fk1 on emp(dept_id);

Repeat the two deletes. Session 2 delete no longer hangs. Rollback in both sessions. And create a composite index.

drop index emp_fk1;

alter table emp add (name varchar2(10));

create index emp_fk2 on emp(dept_id, name);

Repeat the two deletes. Session 2 still does not hang.