Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Adding columns to existing index in Oracle

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?

like image 413
bonsvr Avatar asked Dec 04 '11 21:12

bonsvr


People also ask

How do I add a column to an existing index in Oracle?

To add a column to an existing index: Issue the ALTER INDEX ADD COLUMN SQL statement when you add a column to a table. Commit the alter procedure.

How do I add a column to an existing index in SQL?

Right-click the index that you want to modify and then click Properties. In the Index Properties dialog box, make the desired changes. For example, you can add or remove a column from the index key, or change the setting of an index option.

Can we modify index in Oracle?

You can use ALTER INDEX to perform the following task on all Oracle Text index types: Rename the index or index partition. See ALTER INDEX RENAME Syntax. Rebuild the index using different preferences.


1 Answers

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.

like image 165
Justin Cave Avatar answered Sep 28 '22 15:09

Justin Cave