Adding a column to existing index definition

[nshen]

After running an index analysis it was suggesting to add a fifth column into an existing four-column index.
The base table has over 500 million rows so I'm hesitated to drop the old index then create the new index.
Is there an efficient way to alter the existing index to include the fifth column?

Thank you.
Regards, Nancy

 

[SantaMufasa]

Nancy,

Although your desire to simply add the fifth column to the index seems like it would save time, it would actually be a performance nightmare and here's why:

1) You would still need to read, then re-write, every index block in the table.
2) Oracle would have to take extra time to "step its way" into each row to "squeeze in" the new data to the properly displaced location in the index entry.
3) The newly added row might actually change the collation order of the index entries.
4) Depending upon the size of the newly added column, it would create chained index entries to overflow blocks, causing significant inefficiency/degradation in index-access performance.

Therefore, Oracle does not support an "ALTER INDEX..." option that allows the addition of a new column.

So, your best (only) option is to "DROP INDEX <index_name>;", then "CREATE INDEX..." with the new column. You can, however, accrue performance benefits by using the "PARALLEL" option:

CREATE INDEX <index_name> ON <table_name> (<col1>,<col2>,<col3>,<col4>,<col5>) [B]PARALLEL[/B];"

Let us know the outcome.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[nshen]

Thank you, Dave!
PARALLEL was an excellent suggestion!!

Regards, Nancy