Does making a primary key in multiple columns generate indexes for all of them?

Question

If I set a primary key in multiple columns in Oracle, do I also need to create the indexes if I need them?

I believe that when you set a primary key on one column, you have it indexed by it; is it the same with multiple column PKs?

Thanks

Answer 1

No, indexes will not be created for the individual fields.

If you have a composit key FieldA, FieldB, FieldC and you

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)

Answer 2

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:

alter table mytable add constraint mytable_pk 
primary key (a, b, c)
using index mytable_index;