未加索引的外键(unindexed foreign keys)

英文原文和主要观点节选自Thomas Kyte《Expert.Oracle.Database.Architecture.9i.and.10g.Programming.Techniques.and.Solutions》一书的第6章locking and latching，本人在开发环境做了验证。

 

Oracle will place a full table lock on a child table after modification of the parent table in two cases:

• If you update the parent table’s primary key (a very rare occurrence if you follow the rule of relational databases stating that primary keys should be immutable), the child table will be locked in the absence of an index on the foreign key.

• If you delete a parent table row, the entire child table will be locked (in the absence ofan index on the foreign key) as well.

These full table locks are a short-term occurrence in Oracle9i and above, meaning they need to be taken for the duration of the DML operation, not the entire transaction. Even so,they can and do cause large locking

 

修改外键父表后,oracle在两种情况下会发生子表的全表锁

 

1）  update父表主键（如果遵从关系型数据库主键不变的原则的话会很少发生），如子表的外键未加索引，将被全表锁

2）  如delete父表一行且子表外键未加索引，也会全表锁子表

 

这些全表锁在oracle 9i及以上版本是短期发生的。意味着子表的全表锁发生于父表的DML操作（前面提到的update和delete）期间而非整个事务。尽管如此，当外键没有索引子表较大时，相应的父表DML操作检查子表的数据一致性耗时也较长，一旦全表锁持续较长时间，对子表做其他DML操作的session等待得也就越多，数据库的并发性和性能会受到影响。

 

SQL> create table p ( x int primary key );

Table created

创建父表

 

SQL> create table c ( x references p );

Table created

创建子表

 

SQL> insert into p values ( 1 );

 1 row inserted

SQL> insert into p values ( 2 );

 1 row inserted

父表插入数据

 

SQL> insert into c values ( 2 );

 1 row inserted

子表插入数据

 

这时再开一个窗口，另起session

SQL> delete from p where x = 1;

会发现处于block等待状态，直到前面的窗口做了commit,对父表的删除才能成功执行，这是因为前一个窗口不commit，后面的窗口无法获得子表的全表锁

 

 

SQL> create index c_x on c(X);

 

Index created

 

SQL> select * from c  where x=2 for update;

 

                                      X

---------------------------------------

                                      2

给子表的外键字段创建索引，再锁住一行

 

再开新的session

SQL> delete from p where x = 1;

1 row deleted

删除成功，说明在外键有索引的情况下，不再需要子表的全表锁

 

 

So, when do you not need to index a foreign key? The answer is, in general, when the following conditions are met:

• You do not delete from the parent table.

• You do not update the parent table’s unique/primary key value (watch for unintended  updates to the primary key by tools!).

• You do not join from the parent to the child (like DEPT to EMP).

If you satisfy all three conditions, feel free to skip the index—it is not needed. If you meet

any of the preceding conditions, be aware of the consequences. This is the one rare instance when Oracle tends to “overlock” data.

 

书中描述了哪些情况下无需创建外键的索引，相应的，存在以下需要对外键创建索引的情形：

1）   需对父表delete

2）   Update父表主键或者存在唯一性约束的字段（需考虑一些sql自动生成工具）

3）   父子表关联，通过父表查询子表

 

将测试案例中父表的主键改为唯一性约束字段，测试结果一致。