17

I have a relatively large table (81M rows) and an index on it.

I want to add a column to the existing index.

I searched for it on Google, but I couldn't find a way for it.

I've read somewhere that the only way to add a column to an index is to drop and recreate it.

However, here it says it's common practice to add columns to existing index. (Although the author doesn't recommend it.)

So, is it possible to add columns to existing index and if possible is it good practice?

bonsvr
  • 2,262
  • 5
  • 22
  • 33
  • Taking a look at the Oracle® Database SQL Language Reference 11g Release 1 (http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_1009.htm), there is no way you can use `alter index` to add a column to an existing index. – Raihan Dec 04 '11 at 21:35

1 Answers1

21

It is not possible to add a new column to an existing index without dropping and recreating the index.

When Jonathan Lewis is talking about "adding a column to an existing index", he's talking about dropping the existing index and creating a new index. Note in his example, both the "original index" and "modified index" are listed with a CREATE INDEX statement. There are no ALTER INDEX statements in the example that would add a new column without dropping the old column.

Whether it is a good idea to drop & recreate the index with an additional column depends on a number of factors. As Jonathan Lewis points out, there are various situations where adding additional columns will affect the clustering factor of the index and cause some existing queries to perform more poorly. Without knowing anything about your system or the index we're talking about, it's impossible to advise.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • I am confused as Mr. Lewis talks about a dropped index as `existing` index. Also, I searched for a `DROP INDEX` statement in the mentioned example. I that book is too professional for me as it doesn't need to explain "trivial" things :) Thanks for clarification. – bonsvr Dec 04 '11 at 21:40