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

oracle indexing primary-key multiple-columns
juan picture juan · Feb 10, 2009 · Viewed 17.1k times · Source

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

Binary Worrier picture Binary Worrier · Feb 10, 2009

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)

Related questions

Creating index/pk in a huge table is taking too long. I am using Oracle. How do I know if it is going well?

I have a really huge table, with ~200 million rows. It had no index/pk at all. Selects in this table were (obviously) running slow. I decided to create a PK using 3 columns. I did it in a test environment that …

Read More
Force index use in Oracle

I encountered this question in an interview and had no clue how to answer: There is a table which has a index on a column, and you query: select * from table_name where column_having_index="some value"; The query …

Read More
Oracle - Select where field has lowercase characters

I have a table, users, in an Oracle 9.2.0.6 database. Two of the fields are varchar - last_name and first_name. When rows are inserted into this table, the first name and last name fields are supposed to be in …

Read More