7

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 ?

Shailesh Pratapwar
  • 3,605
  • 3
  • 24
  • 40
  • Good question. I would expect a "not recommended" in that sentence. Perhaps Oracle only creates the index once, and it is recommended in case the integrity constraint changes. – Gordon Linoff May 19 '17 at 13:17
  • 1
    @GordonLinoff - keeping the index when the constraints change is a good reason; however, for that very purpose, Oracle has the `KEEP INDEX` option when disabling constraints. – mathguy May 19 '17 at 14:18

2 Answers2

1

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

mathguy
  • 37,873
  • 5
  • 22
  • 47
1

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

Kacper
  • 4,773
  • 2
  • 15
  • 31
  • There are other differences - you can control storage, etc. with explicit index creation. Not so when the system does it for you. – mathguy May 19 '17 at 14:11