CODEWITHSUNDEEP
sqloracle-databasedatabase-indexes
Shailesh Pratapwar
Shailesh Pratapwar

Reputation: 4224

Creating indexes on columns where primary key or unique key constraints already present

I am currently learning indexes in Oracle. In Oracle documentation, following can be found :

Although the database creates an index for you on a column with an integrity constraint, explicitly creating an index on such a column is recommended.

Can anybody here tell me the rationale behind this ? If database creates an index automatically, why should we create another index on those columns explicitly ?

Upvotes: 7

Views: 92

Answers (2)

Kacper
Kacper

Reputation: 4818

The only idea I have is those automatically created indexes got some weird names either generated as SYS% or inheriting names from constraint name. It is probably better to keep some naming convention for indexes

Upvotes: 1

user5683823
user5683823

Reputation:

At the very least, if you create an index explicitly, you can give it a meaningful name. If you then need to refer to the index by name, it will be in a human-readable form.

Note that you can create an explicit index WHILE you are creating the constraint, in the USING INDEX clause. The documentation has a few examples: https://docs.oracle.com/cd/B28359_01/server.111/b28310/indexes003.htm#ADMIN11725

Upvotes: 1

Related Questions