ALTER INDEX To Add the Extra columns [message #676757] |
Fri, 12 July 2019 08:05 |
|
saipradyumn
Messages: 419 Registered: October 2011 Location: Hyderabad
|
Senior Member |
|
|
Hi All,
Can we alter the INDEX to add more columns to the existing INDEX. My table is having 5 Million records.
Recently we add new columns to the table. Need to add indexes on those tables.
Instead of creating the new indexes want to make use the existing one by adding the extra columns.
Thanks
SaiPradyumn
|
|
|
|
|
Re: ALTER INDEX To Add the Extra columns [message #676763 is a reply to message #676758] |
Fri, 12 July 2019 13:14 |
John Watson
Messages: 8937 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
You do seem to have a habit of asking for help, and NEVER responding with any appropriate feedback. Do you ever help anyone? Or make posts that another person would find helpful? If you changed this you might find that your colleagues were happier to work with you, that you receive better responses on forums, and that you boss gives you a pay rise.
|
|
|
Re: ALTER INDEX To Add the Extra columns [message #676811 is a reply to message #676763] |
Thu, 18 July 2019 13:33 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
Why not simply make a new index with all you required columns and then drop the old index. In later versions you can use the ONLINE clause so it can be built or dropped while the table is in use. The ALTER INDEX can't be used to change the column list.
|
|
|
Re: ALTER INDEX To Add the Extra columns [message #676812 is a reply to message #676757] |
Thu, 18 July 2019 14:08 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
saipradyumn wrote on Fri, 12 July 2019 18:35Can we alter the INDEX to add more columns to the existing INDEX. My table is having 5 Million records.
Recently we add new columns to the table. Need to add indexes on those tables.
Instead of creating the new indexes want to make use the existing one by adding the extra columns.
Your first question is whether it is semantically possible. So, did you try to test in your DEV or similar environment if you have?
If you have had been doing similar dev/testing for any kind of immediate business requirements, then it's time to take a pause and think about long-time impact. As other members have already said, ALTER INDEX won't let you achieve your requirement on the fly as you are expecting. Please revisit the requirements and design.
If it's a one time activity, go ahead with DROP and CREATE a new index. However, you must keep in mind that it might affect other queries using the existing index.
|
|
|