主页 / dba / 问题 / 28518
Accepted
a1ex07
Asked: 2012-11-12 08:55:25 +0800 CST

Oracle 中一个索引支持 2 个约束是否有任何潜在问题？

  • 772

我有下表：

CREATE TABLE action (action_id INT NOT NULL,
action_type_id INT NOT NULL,
action_date DATE NOT NULL);
CREATE INDEX IDX_ACTION_ACT_TYPE_ACT ON action(action_id, action_type_id);
ALTER TABLE action ADD CONSTRAINT PK_ACTION PRIMARY KEY (action_id) 
  USING INDEX IDX_ACTION_ACT_TYPE_ACT;
ALTER TABLE action ADD CONSTRAINT UQ_ACTION UNIQUE (action_id,action_type_id) 
  USING INDEX IDX_ACTION_ACT_TYPE_ACT;
-- I think the line below is irrelevant in the context of my question, but 
-- in case I'm wrong... 
ALTER TABLE action ADD CONSTRAINT FK_action_type_id FOREIGN KEY(action_type_id) 
REFERENCES action_type(action_type_id);
-- Since action_type is a lookup added for normalization sake with no updates/deletes,
-- I don't see any value in having an index on FK column.

它是许多明细表的公共父表，这些明细表存储特定操作的额外信息。action_type_id是唯一约束的一部分，让详细表具有 FK 到 (action_id,action_type_id) 并强制每个详细表只存储它应该存储的信息。

我想知道与创建 2 个索引相比，在 2 个约束中使用相同的索引是否有任何性能损失......在我看来它应该比 2 个索引表现得更好，但也许我遗漏了一些东西。如果重要的话，我正在使用 Oracle 10g。

谢谢您的回答。

oracle-10g index

1 个回答

  1. Best Answer

    正如 Nat 所指出的，您的 UQ_ACTION 约束复制了主键约束。根据您在此表中的 FK 用法，您的主键是 (action_id, action_type_id)。因此它是多余的，应该被删除。如果您希望您的主键单独为 action_id，那么您的外键必须仅引用 action_id。

    我相信您将 action_id 建模为主键是正确的。在这种情况下，更改您的孩子参考资料。（在外键中使用操作类型 id 违反了正常形式，并强制将冗余信息放入引用表中。）。如果它们适用于多种类型，则操作表的子表将在其键中包含操作类型 ID。

    您可能需要 action_id 上的 UK，但这不是必需的。如果你有这样一个带有相应索引的 UK，那么我会反转包含 (action_type_id, action_id) 的索引的顺序。这将允许仅通过 action_id 和 action_type_id 进行快速检索，其中统计信息表明索引是有用的。

    但是，要在索引可用于多个约束的情况下回答您的问题，没有任何惩罚。事实上，情况恰恰相反。

    维护索引可能是一项相对昂贵的操作。添加冗余索引确实有代价。每个更改冗余索引中的列的插入、更新或删除都需要额外的工作来更新索引。

    看来您已经认识到不创建未使用的索引的价值。虽然很常见，但在许多情况下外键索引并不是必需的。如果要从引用表中删除，最好有一个索引。以外键开头的代理索引可能会导致更高效的查询。如果您经常按 action_type_id 进行查找，索引将很有用。使用具有其他值的多列索引来过滤结果集可能更有用。

    • 0

相关问题