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?
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.
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.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With