SQL 我需要在 Oracle 的外键上创建索引吗？

Do I need to create indexes on foreign keys on Oracle?

提问by aw crud

I have a table Aand a table B. Ahas a foreign key to Bon B's primary key, B_ID.

我有一张桌子A和一张桌子B。 A有一个BonB的主键的外键，B_ID。

For some reason (I know there are legitimate reasons) it is not using an index when I join these two tables on the key.

出于某种原因（我知道有正当理由），当我在键上连接这两个表时，它没有使用索引。

Do I need to separately create an index on A.B_IDor should the existence of a foreign key provide that?

我是否需要单独创建索引，A.B_ID或者外键的存在是否应该提供索引？

回答by DCookie

The foreign key constraint alone does not provide the index on Oracle - one must (and should) be created.

外键约束本身并不提供 Oracle 上的索引 - 必须（并且应该）创建索引。

回答by Justin Cave

Creating a foreign key does not automatically create an index on A.B_ID. So it would generally make sense from a query performance perspective to create a separate index on A.B_ID.

创建外键不会自动在 A.B_ID 上创建索引。因此，从查询性能的角度来看，在 A.B_ID 上创建单独的索引通常是有意义的。

If you ever delete rows in B, you definitely want A.B_ID to be indexed. Otherwise, Oracle will have to do a full table scan on A every time you delete a row from B to make sure that there are no orphaned records (depending on the Oracle version, there may be additional locking implications as well, but those are diminished in more recent Oracle versions).

如果你曾经删除过 B 中的行，你肯定希望 A.B_ID 被索引。否则，每次从 B 中删除一行时，Oracle 都必须对 A 进行全表扫描，以确保没有孤立记录（取决于 Oracle 版本，可能还会有额外的锁定影响，但这些影响已减少）在最近的 Oracle 版本中）。

回答by Jeffrey Kemp

Just for more info: Oracle doesn't create an index automatically (as it does for unique constraints) because (a) it is not required to enforce the constraint, and (b) in some cases you don't need one.

只是为了更多信息：Oracle 不会自动创建索引（就像它为唯一约束所做的那样），因为 (a) 不需要强制执行约束，并且 (b) 在某些情况下您不需要一个。

Mostof the time, however, you will want to create an index (in fact, in Oracle Apex there's a report of "unindexed foreign keys").

然而，大多数情况下，您会想要创建一个索引（实际上，在 Oracle Apex 中有一个“未索引外键”的报告）。

Whenever the application needs to be able to delete a row in the parent table, or update the PK value (which is rarer), the DML will suffer if no index exists, because it will have to lock the entire child table.

每当应用程序需要能够删除父表中的一行，或更新 PK 值（这种情况很少见）时，如果不存在索引，DML 就会受到影响，因为它必须锁定整个子表。

A case where I usually choose notto add an index is where the FK is to a "static data" table that defines the domain of a column (e.g. a table of status codes), where updates and deletes on the parent table are never done directly by the application. However, if adding an index on the column gives benefits to important queries in the application, then the index will still be a good idea.

我通常选择不添加索引的情况是 FK 指向定义列域的“静态数据”表（例如状态代码表），其中父表上的更新和删除永远不会完成直接通过应用程序。但是，如果在列上添加索引对应用程序中的重要查询有好处，那么索引仍然是一个好主意。

回答by marc_s

SQL Server has never put indexes onto foreign key columns automatically - check out Kim Tripp's excellent blog poston the background and history of this urban myth.

SQL Server 从未自动将索引放在外键列上 - 请查看 Kim Tripp关于这个城市神话的背景和历史的优秀博客文章。

It's usually a good idea to index your foreign key columns, however - so yes, I would recommend making sure each FK column is backed up by an index; not necessarily on that one column alone - maybe it can make sense to create an index on two or three columns with the FK column as the first one in there. Depends on your scenario and your data.

然而，索引外键列通常是个好主意 - 所以是的，我建议确保每个 FK 列都由索引备份；不一定只在这一列上 - 也许在两列或三列上创建索引是有意义的，其中 FK 列作为其中的第一列。取决于您的场景和数据。

回答by bubi

For performance reasons an index should be created. Is used in delete operations on primary table (to check that the record you are deleting is not used) and in joins that usually a foreign key is involved. Only few tables (I do not create them in logs) could be that do not need the index but probably, in this cases probably you don't need the foreign key constraint as well.

出于性能原因，应该创建一个索引。用于主表的删除操作（检查您正在删除的记录是否未被使用）以及通常涉及外键的连接。只有少数表（我不在日志中创建它们）可能不需要索引，但在这种情况下，您可能也不需要外键约束。

BUT

但

There are some databases that already automatically create indexes on foreign Keys. Jet Engine (Microsoft Access Files) Firebird MySQL

有一些数据库已经自动在外键上创建索引。Jet Engine (Microsoft Access Files) Firebird MySQL

FOR SURE

当然

SQL Server Oracle

SQL Server 甲骨文

DOES NOT

才不是

回答by Chinmai

UNIQUE, PRIMARY KEY, and FOREIGN KEY constraints generate indexes that enforce or "back" the constraint (and are sometimes called backing indexes). PRIMARY KEY constraints generate unique indexes. FOREIGN KEY constraints generate non-unique indexes. UNIQUE constraints generate unique indexes if all the columns are non-nullable, and they generate non-unique indexes if one or more columns are nullable. Therefore, if a column or set of columns has a UNIQUE, PRIMARY KEY, or FOREIGN KEY constraint on it, you do not need to create an index on those columns for performance.

UNIQUE、PRIMARY KEY 和 FOREIGN KEY 约束生成强制或“支持”约束的索引（有时称为支持索引）。PRIMARY KEY 约束生成唯一索引。FOREIGN KEY 约束生成非唯一索引。如果所有列都不可为空，UNIQUE 约束会生成唯一索引，如果一列或多列可以为空，则它们生成非唯一索引。因此，如果一列或一组列上有 UNIQUE、PRIMARY KEY 或 FOREIGN KEY 约束，则不需要在这些列上创建索引以提高性能。

回答by onedaywhen

As with anything relating to performance, it depends on many factors and there is no silve bullet e.g. in a very high activilty environment the maintainance of an index may be unacceptable.

与任何与性能相关的事情一样，它取决于许多因素，并且没有灵丹妙药，例如在非常高的活动环境中，索引的维护可能是不可接受的。

Most salient here would seem to be selectivity: if the values in the index would be highly duplicated then it may give better performance to drop the index (if possible) and allow a table scan.

这里最突出的似乎是选择性：如果索引中的值高度重复，那么删除索引（如果可能）并允许表扫描可能会提供更好的性能。