Re: Can a deferred FK constraint cause "enq: TX - row lock contention" in Share (4) mode?

> To build such a foreign key constraint when referenced values are missing it's
> necessary to also specify NOVALIDATE to avoid the "ORA-02298: cannot validate (string) - parent keys not found" error.

No, it's not - when you do when creating the table. It's used so that you don't need to think about the order of inserting rows.

But if that does cause that waits we might as well re-think that decision.

> When such a constraint is successfully created it does take longer to perform updates on the child table.
> However in 11.2.0.3 no such waits are listed when the child table is updated and an index exists for the foreign key column according to my tests.

Hmm, I am aware about indexing FK constraints and the waits on the child _table_ when updating the the parent. But in this case the waits are on the _index_ of the child table, that's what confuses me.

> It could be 11.2.0.2 specific behavior ('bug').

I haved searched MOS, but couldn't find anything (but that might just mean I searched with the wrong keywords).

Thomas

--
http://www.freelists.org/webpage/oracle-l