Oracle Table Index creation

Question

I created a table named STUDENT. It has the following columns:

Id
Name
Surname
DateOfBirth
DateOfAdmission
DateOfPassout

This table have following primary key:

Id
Name
Surname
DateOfAdmission

Do I need to create an index another index of column Id, Name if want to query this table providing input just Id and Name?

Does your primary key really need four columns? Judging by the names, it looks like Id should be the primary key (I presume it is always unique). Understand that if your primary key really does consist of four columns, you will end up with a lot of columns in other tables for foreign key constraints. — Adam Paynter
– Adam Paynter, Commented Jan 13, 2011 at 9:27

OMG Ponies · Accepted Answer · 2011-01-13 06:47:38Z

2

An index isn't necessary for queries.
An index has the potential to speed up queries if the index can be used, but will slow down INSERT/UPDATE/DELETE statements.

I'm not clear when Oracle started, but Oracle 10g+ will automatically create an index when a primary key is defined for a table. That index will match the column(s) that make up the primary key. Being that the id and name columns are part of the primary key, the pair is guaranteed to be unique and I don't see the need to create an additional covering index.

answered Jan 13, 2011 at 6:47

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle Table Index creation

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related