Home » SQL & PL/SQL » SQL & PL/SQL » ALTER INDEX To Add the Extra columns (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production)
ALTER INDEX To Add the Extra columns [message #676757] Fri, 12 July 2019 08:05 Go to next message
saipradyumn
Messages: 400
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
ALTER INDEX To Add the Extra columns [message #676758 is a reply to message #676757] Fri, 12 July 2019 08:05 Go to previous messageGo to next message
saipradyumn
Messages: 400
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 #676761 is a reply to message #676757] Fri, 12 July 2019 10:27 Go to previous messageGo to next message
Michel Cadot
Messages: 66516
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I can't help, I'd like to do it but I'm blocked waiting for your feedback in your previous topics.

[Updated on: Fri, 12 July 2019 10:27]

Report message to a moderator

Re: ALTER INDEX To Add the Extra columns [message #676763 is a reply to message #676758] Fri, 12 July 2019 13:14 Go to previous messageGo to next message
John Watson
Messages: 7993
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 Go to previous messageGo to next message
Bill B
Messages: 1880
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 Go to previous message
Lalit Kumar B
Messages: 3149
Registered: May 2013
Location: World Wide on the Web
Senior Member
saipradyumn wrote on Fri, 12 July 2019 18:35
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.
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.
Previous Topic: Purging Data From table!
Next Topic: How to get the file name from directory
Goto Forum:
  


Current Time: Wed Aug 21 17:20:26 CDT 2019