外键创建索引

Oracle中更新主外键时的一些注意事项

1、外键无索引时，子表更新外键未提交，主表更新非子表引用的主键时被阻塞

会话1：

create table t1 (x int primary key);

insert into t1 values(1);

insert into t1 values(2);

insert into t1 values(3);

commit;

create table t2（y int references t1）；

insert into t2 values（1）；

commit;

update t2 set y=2 where y=1;

会话2：

update t1 set x=4 where x=3; //会话被阻塞

2、外键有索引时，子表更新外键未提交，主表更新非子表引用的主键时不会被阻塞

会话1：

create index t2_index on t2(y) ; //创建外键索引

update t2 set y=2 where y=1;

会话2：

update t1 set x=4 where x=3;

已更新 1 行；//可以正常更新

3、外键有无索引，对于子表更新外键未提交，主表更新相对应的主键无影响，更新主键的session都会被阻塞

会话1：

update t2 set y=2 where y=1;

会话2：

update t1 set x=4 where x=1; //更新子表已引用的

会话被阻塞。

会话1：

update t2 set y=2 where y=1;

会话2：

update t1 set x=4 where x=2 ; //更新子表将要引用的

会话被阻塞。――很好理解，主表要判断是否违反约束

二、更新子表非外键列未提交

1、外键无索引，更新主表已被外键引用的主键时，更新主键的session被阻塞

会话1：

create table t1 (x int primary key,x1 int);

insert into t1 values(1,1);

insert into t1 values(2,2);

insert into t1 values(3,3);

commit ;

create table t2(y int references t1,y1 int);

insert into t2 values(1,1);

commit ;

update t2 set y1=2 where y1=1;

会话2：

update t1 set x=4 where x=1; //更新外键引用的主键

会话被阻塞。

2、外键有索引，更新主表已被外键引用的主键时，更新主键的session不会被阻塞而报约束错误

会话1：

create index t2_index on t2(y);

update t2 set y1=2 where y1=1;

会话2：

update t1 set x=4 where x=1

*

ERROR 位于第 1 行:

ORA-02292: 违反完整约束条件 (SCOTT.SYS_C001607) - 已找到子记录日志

3、外键无索引，更新主表未被外键引用的主键时，更新主键的session被阻塞

会话1：

drop index t2_index；

update t2 set y1=2 where y1=1;

会话2:

update t1 set x=4 where x=2;

会话被阻塞。

4、外键有索引，更新主表未被外键引用的主键时，更新主键的session不会被阻塞

会话1：

create index t2_index on t2(y);

update t2 set y1=2 where y1=1;

会话2：

update t1 set x=4 where x=2;已更新 1 行。

另外在一个主表有on delete cascade，子表没有外键索引时，对主表操作会级联到子表，子表将进行全表扫描。

总结：在需要更新主键的情况下，最好是创建子表的外键索引。

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22934571/viewspace-1047245/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22934571/viewspace-1047245/