oracle 在多列中创建主键是否会为所有列生成索引？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/532037/
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
Does making a primary key in multiple columns generate indexes for all of them?
提问by juan
If I set a primary key in multiple columns in Oracle, do I also need to create the indexes if I need them?
如果我在 Oracle 的多个列中设置主键，是否还需要创建索引（如果需要）？
I believe that when you set a primary key on onecolumn, you have it indexed by it; is it the same with multiple column PKs?
我相信当你在一列上设置一个主键时，你就会用它来索引它；多列 PK 是否相同？
Thanks
谢谢
回答by Binary Worrier
No, indexes will not be created for the individual fields.
不，不会为各个字段创建索引。
If you have a composit key FieldA, FieldB, FieldC and you
如果你有一个复合键 FieldA、FieldB、FieldC 并且你
select * from MyTable where FieldA = :a
or
或者
select * from MyTable where FieldA = :a and FieldB = :b
Then it will use this index (because it they are the first two fields in the key)
然后它将使用这个索引（因为它们是键中的前两个字段）
If you have
如果你有
select * from MyTable where FieldB = :b and FieldC = :c
Where you are using parts of the index, but not the full index, the index will be used less efficiently through an index skip scan, full index scan, or fast full index scan.
如果您使用部分索引而不是完整索引，则通过索引跳过扫描、完整索引扫描或快速完整索引扫描，索引的使用效率会降低。
(Thanks to David Aldridge for the correction)
（感谢大卫奥尔德里奇的更正）
回答by Tony Andrews
If you create a primary key on columns (A, B, C) then Oracle will by default create a unique index on (A, B. C). You can tell Oracle to use a different (not necessarily unique) existing index like this:
如果您在列 (A, B, C) 上创建主键，那么 Oracle 将默认在 (A, B. C) 上创建唯一索引。您可以告诉 Oracle 使用不同的（不一定是唯一的）现有索引，如下所示：
alter table mytable add constraint mytable_pk
primary key (a, b, c)
using index mytable_index;
回答by Darron
You will get one index across multiple columns, which is not the same as having an index on each column.
您将获得跨多列的一个索引，这与在每一列上都有一个索引不同。
回答by Quassnoi
Primary key implies creating a composite unique indexon primary key columns.
主键意味着composite unique index在主键列上创建一个。
You can use a special access path called INDEX SKIP SCANto use this index with predicates that do not include the first indexed column:
您可以使用一个特殊的访问路径INDEX SKIP SCAN，通过不包含第一个索引列的谓词来使用这个索引：
SQL> CREATE TABLE t_multiple (mul_first INTEGER NOT NULL, mul_second INTEGER NOT NULL, mul_data VARCHAR2(200))
2 /
Table created
SQL> ALTER TABLE t_multiple ADD CONSTRAINT pk_mul_first_second PRIMARY KEY (mul_first, mul_second)
2 /
Table altered
SELECT /*+ INDEX_SS (m pk_mul_first_second) */
*
FROM t_multiple m
WHERE mul_second = :test
SELECT STATEMENT, GOAL = ALL_ROWS
TABLE ACCESS BY INDEX ROWID SCOTT T_MULTIPLE
INDEX SKIP SCAN SCOTT PK_MUL_FIRST_SECOND
回答by Alex
You may need to set individual indexes on the columns depending on your primary key structure.
您可能需要根据主键结构在列上设置单独的索引。
Composite primary keys and indexes will create indexes in the following manner. Say i have columns A, B, C and i a create the primary key on (A, B, C). This will result in the indexes
复合主键和索引将按以下方式创建索引。假设我有 A、B、C 列，并且 ia 在（A、B、C）上创建主键。这将导致索引
- (A, B, C)
- (A, B)
- (A)
- (A, B, C)
- （甲、乙）
- （一种）
Oracle actually creates an index on any of the left most column groupings. So... If you want an index on just the column B you will have to create one for it as well as the primary key.
Oracle 实际上在最左边的任何列分组上创建索引。所以...如果你只想在 B 列上建立一个索引，你将不得不为它创建一个索引以及主键。
P.S. I know MySQL exibits this left most behaviour and i think SQL Server is also left most
PS 我知道 MySQL exibits 这是最左边的行为，我认为 SQL Server 也是最左边的
回答by Srav
In Oracle, that's not an accurate statement. It creates only 1 index on (A,B,C). Does not create (A,B) and (A) indexes.
在 Oracle 中，这不是一个准确的说法。它仅在 (A,B,C) 上创建 1 个索引。不创建 (A,B) 和 (A) 索引。
回答by tehvan
A primary key is only one (unique) index, possibly containing multiple columns
一个主键只是一个（唯一的）索引，可能包含多个列
回答by tehvan
For B select index will be used if column a have low cardinality only (e.g. a have only 2 values). In general you could have guessed this answer if you imagined that columns not indexed separately, but indexed concatenation of columns (it's not completely true, but it works for first approximation). So it's not a, b index it's more like a||b index.
如果列 a 只有低基数（例如 a 只有 2 个值），则将使用 B 选择索引。一般来说，如果您想象列没有单独索引，而是索引列的串联（这不完全正确，但它适用于第一次近似），您可能已经猜到了这个答案。所以它不是 a, b 索引，它更像是 a||b 索引。
