英文原文和主要观点节选自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 Oracle9iand 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 insertedSQL insert into p values ( 2 );1 row inserted父表插入数据SQL insert into c values ( 2 );1 row inserted子表插入数据这时再开一个窗口另起sessionSQL delete from p where x 1;会发现处于block等待状态直到前面的窗口做了commit,对父表的删除才能成功执行这是因为前一个窗口不commit后面的窗口无法获得子表的全表锁SQL create index c_x on c(X);Index createdSQL select * from c where x2 for update;X---------------------------------------2给子表的外键字段创建索引再锁住一行再开新的sessionSQL delete from p where x 1;1 row deleted删除成功说明在外键有索引的情况下不再需要子表的全表锁So, when do younotneed to index a foreign key? The answer is, in general, when the following conditions are met:• You donotdelete from the parent table.• You donotupdate the parent table’s unique/primary key value (watch for unintended updates to the primary key by tools!).• You donotjoin from the parent to the child (likeDEPTtoEMP).If you satisfy all three conditions, feel free to skip the index—it is not needed. If you meetany of the preceding conditions, be aware of the consequences. This is the one rare instance when Oracle tends to “overlock” data.书中描述了哪些情况下无需创建外键的索引相应的存在以下需要对外键创建索引的情形1需对父表delete2Update父表主键或者存在唯一性约束的字段需考虑一些sql自动生成工具3父子表关联通过父表查询子表将测试案例中父表的主键改为唯一性约束字段测试结果一致。