oracle 未索引的外键导致 TM 入队争用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/351375/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Unindexed Foreign Key leads to TM Enqueue Contention
提问by
So we've been told that one source of TM Enqcontention can be unindexed FK's. My question is which one.
所以我们被告知，一个TM Enq争用源可能是未编入索引的 FK。我的问题是哪个。
I have an INSERT INTO Table_Bthat is recording TM Enq Wait.
我有一个INSERT INTO Table_B正在录音的TM Enq Wait。
It contains a PKthat is the parent to other tables and it has columns that are FKconstrained to other PKs.
它包含一个PK是其他表的父表，并且它具有FK约束到其他PKs 的列。
So which FKs need indexed: that table's columns or its children?
那么哪个FK需要索引：该表的列或其子项？
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.
注意：我知道这不是 TM 争用的唯一原因。如果是这种情况，你能解释为什么它不可能是这样。
回答by derobert
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.
不确定 Oracle TM 争用，但我会说通常外键关系的双方都被编入索引。否则，数据库将不得不进行表扫描。
- 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.
编辑：有谷歌 TM 争用，听起来你可能错过了子记录上的键。但一定要确保两边都有它们，真的。
EDIT 2:Answering the comment,
编辑2：回答评论，
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?
如果您有一个 OLTP 表，它有 13 个 FK 来查找表，那么除了表、pk 和任何其他索引之外，我并不热衷于 13 个索引更新。索引很重要，但出于特定原因。如果你从不更新父PK，也不从父中删除，那么子索引就没那么有用了。或者是吗？
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)
此外，根据http://ashmasters.com/waits/enq-tm-contention/，如果您完全更改父表，您几乎需要拥有索引。显然，除非您有索引，否则您可以通过对父表和子表进行并发更改来获取它们。所以这可能就是你所看到的（假设你没有做明显的事情，比如更新引用的列或删除行）
回答by David Aldridge
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?
你看到的是什么模式的 TM Enqueue？
