1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

how to modify an index created on a table in sql

Discussion in 'SQL PL/SQL' started by bujji_22, May 19, 2010.

  1. bujji_22

    bujji_22 Guest

    hi,

    we have created index1 on column1 of a table in sql. Now we want to add column2 of same table to that index. Can we modify existing index to accomplish this. If so, pls let me know the command for this.

    Thanks...
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    drop index <indexname>;
    create index <indexname> on <tablename>(<colname>,<colname>[,<colname>...]);
     
  3. ashishb01

    ashishb01 Active Member

    Messages:
    13
    Likes Received:
    1
    Trophy Points:
    110
    Lets do it in one shot mate ... fire this query ...... it should work i believe
    Code (SQL):
    ALTER INDEX your_index_name ADD COLUMN (your_column_name);
     
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It won't work as that is not a valid option for alter index, even in 11g:

    Code (SQL):
     
    SQL> ALTER INDEX yazoo_idx ADD COLUMN (florp);
    ALTER INDEX yazoo_idx ADD COLUMN (florp)
                              *
    ERROR at line 1:
    ORA-14004: missing PARTITION keyword

    SQL>

     
    The only way to add a column to an index is to drop it and recreate it.
     
  5. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India