Unindexed Foreign Key leads to TM Enqueue Contention

Question

So we've been told that one source of TM Enq contention can be unindexed FK's. My question is which one.

I have an INSERT INTO Table_B that is recording TM Enq Wait.

It contains a PK that is the parent to other tables and it has columns that are FK constrained to other PKs.

So which FKs need indexed. That table's columns or its childrens'?

NB: I know that this isn't the only cause of TM Contention. Can you explain why it couldn't possibly be this if that's the case.

Answer 1

Not sure about Oracle TM Contention, but I'd say normally both sides of a foreign key relation are indexed. Otherwise, the database will have to do table scans.

• The index on the parent record is used whenever you insert a new child record, to verify that the parent exists. Often this is a primary key as well, so of course has an index.
• The index on the child record is used whenever you change or delete a parent record, to perform cascades (including refusing the update/delete).

The indices on both sides also give the database a good chance of doing fast (indexed) joins, no matter which side its optimizer prefers to come from.

EDIT: Having Googled TM contention, it sounds like you're probably missing the keys on the child records. But make sure to have them on both sides, really.

EDIT 2: Answering the comment,

If you have a OLTP table that has 13 FKs to lookup tables, I'm not keen on 13 index updates in addition to the table, pk and any other indexes. An index is important but for specific reasons. If you never update the parent PK nor delete from the parent, the child index is not so useful. Or is it?

Depends on the joins and queries you're running, then. E.g., if you run a query like:

SELECT o.something
  FROM oltp_tab o JOIN lookup l ON (o.lookup_no = l.lookup_no)
  WHERE l.lookup_name = ?

then the query optimizer would probably like the index on the child records.

---

Also, according to http://ashmasters.com/waits/enq-tm-contention/ you pretty much need to have the indices if you change the parent tables at all. Apparently you get them from having concurrent changes to the parent and child tables, unless you have the index. So this is probably what you're seeing (assuming you're not doing the obvious things, like updating the referred to columns or deleting rows)

Answer 2

The parent (referenced) column of an enabled foreign key relationship has to be indexed because it has to have an enabled unique or primary key constraint on it.

What mode of TM Enqueue are you seeing?