Oracle外键无索引系列之一

在数据库的运维过程中偶尔会碰到外键无索引而导致数据库会话被阻塞，前台无法访问数据的情况。此种情况在我司也出现过，所以萌生了写此系列文章的想法。通过各种实验场景来进行探讨，那些DML操作在外键无索引的情况下会造成阻塞，此系列文章适合开发、运维和DBA阅读。

准备测试用的表test_pk1, test_fk1 并进行外键关联。

###########

create table test_pk1 (id number,pcol varchar2(1)) tablespace users;

alter table test_pk1 add constraint pk_test_pk1_id primary key(id) using index tablespace users;

insert into test_pk1 values(1,'A');

insert into test_pk1 values(2,'B');

insert into test_pk1 values(3,'C');

insert into test_pk1 values(4,'D');

insert into test_pk1 values(5,'E');

commit;

############

create table test_fk1 (id number,fcol varchar2(1)) tablespace users;

alter table test_fk1 add constraint fk_test_fk1_id foreign key(id) references test_pk1(id);

insert into test_fk1 values(1,'a');

insert into test_fk1 values(2,'a');

commit;

查询新建的表：

Test_pk1表的内容：

Test_fk1表的内容：

对表的DML操作情况有很多种，此次只会进行一种场景的测试，我们给此次测试标注为场景一，即对子表test_fk1上的DML操作执行后不提交，对之后在主表test_pk1上发起的DML操作是否会造成阻塞。

场景一：子表执行insert操作后不提交，另一个事务对主表进行update、delete、insert。

①　insert子表test_fk1

insertintotest_fk1values(5,'d');

②　分别对主表test_pk1进行update、delete、insert 操作看是否存在阻塞。updatetest_pk1setid=55whereid='4';

delete test_pk1 where id='4';

insert into test_pk1 values(6,’F’);

可以看到在对子表insert操作未提交的情况下，会阻塞后续对主表进行update（针对主键的update），delete 操作，主表上的insert操作不受影响。

③　给外键加索引后，然后进行主表的update、delete、insert操作看是否会有影响。

create index idx_id on test_fk1(id);

select * from user_indexes a where a.table_name='TEST_FK1';

④　索引创建后对字表进行insert操作不提交，然后进行主表的update、delete、insert操作。

insert into test_fk1 values(5,'d');

对主表进行update操作：

update test_pk1 set id=55 where id='4';

对主表进行delete操作：

delete test_pk1 where id='4';

对主表进行insert操作：

insert into test_pk1 values(6,’F’);

场景一结论：

子表上的insert 操作执行后不提交，会阻塞后续主表上的update(针对主键的update)、delete操作，主表上的insert操作不受影响，外键加索引后消除阻塞问题。

后续会有Oralce之外键无索引系列二、三、四...