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

You will get one index across multiple columns, which is not the same as having an index on each column.

Answer 2

A primary key is only one (unique) index, possibly containing multiple columns

Answer 3

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 effentially through an index skip scan, full index scan, or fast full index scan.

(Thanks to David Aldridge for the correction)

Answer 4

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;

Answer 5

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.

Answer 6

Primary key implies creating a composite unique index on primary key columns.

You can use a special access path called INDEX SKIP SCAN to use this index with predicates that do not include the first indexed column:

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

Answer 7

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)
• (A, B)
• (A)

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.

P.S. I know MySQL exibits this left most behaviour and i think SQL Server is also left most

Answer 8

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.