Forum Stats

  • 3,780,511 Users
  • 2,254,405 Discussions
  • 7,879,364 Comments

Discussions

Alter Index to add additional columns

Saro
Saro Member Posts: 758
edited Apr 11, 2014 4:34PM in SQL & PL/SQL

Hi friends,

Is it possible to alter an unique index inorder to add the additional column to it, instead of dropping and recreating it.

Currently i have an index with the below stmt create stmt

CREATE UNIQUE INDEX PK_D_REG_CAND_SYL_DIMENSION ON BEC_DW_NEW.D_REG_CAND_SYL_DIMENSION
(FK_QUALIFICATION_ID, FK_SERIES_ID, FK_CANDIDATE_MST_ID, FK_SYLLABUS_ID, FK_DIMENSION_TYPE_ID)

Now i need to add an extra column to the above (i.e) PK_CAND_SYLLABUS_DIMENSION_ID. Is it possible to alter the above index to include the additional columns.

Will the below stmt help

ALTER INDEX PK_D_REG_CAND_SYL_DIMENSION ADD PK_CAND_SYLLABUS_DIMENSION_ID

Thanks in advance.

Regards,

Saro

Tagged:
User_LCHKL

Answers

  • Martin Preiss
    Martin Preiss Member Posts: 2,381 Gold Trophy

    no, it's not possible to add a column to an existing index. Since the index structure will change fundamentally a rebuild can not be avoided.

    Martin Preiss
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,370 Red Diamond

    Hi, Saro,

    Saro wrote:
    
    Hi friends,
    
    Is it possible to alter an unique index inorder to add the additional column to it, instead of dropping and recreating it. ...
    
    

    No, I don't believe it is possible.  You'll have to drop and recreate the index.

    User_LCHKLFrank Kulash
  • John Stegeman
    John Stegeman Member Posts: 24,269 Blue Diamond

    In addition to Martin's answer, you could have easily found this out by reading the docs. All syntax is explained there in great detail

    John Stegeman
  • Saro
    Saro Member Posts: 758

    Thanks for all your replies and confirmation. Will recreate the index.

    Cheers!!!

    Saro

  • Now i need to add an extra column to the above (i.e) PK_CAND_SYLLABUS_DIMENSION_ID. Is it possible to alter the above index to include the additional columns.

    Can we assume you have completed your testing to see what ADVERSE impacts there might be on your queries and system by adding that column to a unique index?

    Why are  you adding a column to a UNIQUE index instead of creating a new index?

    If you really need an index with both the old and a new column just create a NEW index with the extra column and then drop the old index.

This discussion has been closed.