Forum Stats

  • 3,782,041 Users
  • 2,254,585 Discussions
  • 7,879,899 Comments

Discussions

Categories

How to alter index

586651
586651 Member Posts: 15
edited Sep 18, 2008 2:49AM in SQL & PL/SQL
how to alter index example is
un1 -- index name
xx_test -- Table name
a1,a2,a3 are the column names of table xx_test
create unique index un1 on xx_test (a1)
i want to alter index un1 on a1 to un1 (a1,a2,a3) on table xx_test

Thanks in Advance
Tagged:
· Share on TwitterShare on Facebook

Answers

  • Tubby
    Tubby Member Posts: 6,987 Bronze Crown
    Drop index un1 ;
    create index un1 on xx_test (a1,a2,a3) ;
    · Share on TwitterShare on Facebook
  • 586651
    586651 Member Posts: 15
    I dont want to drop the index is it possible to alter?
    · Share on TwitterShare on Facebook
  • 659537
    659537 Member Posts: 309
    yes you can alter index with "alter index" syntax to change.
    You can alter index for :-
    Rebuild or coalesce an existing index
    Deallocate unused space or allocate a new extent
    Specify parallel execution (or not) and alter the degree of parallelism
    Alter storage parameters or physical attributes
    Specify LOGGING or NOLOGGING
    Enable or disable key compression
    Mark the index unusable
    Start or stop the monitoring of index usage

    -----You cannot alter an index's column structure.---------

    For more details check in the documentation
    http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/indexes.htm#518
    · Share on TwitterShare on Facebook
  • 659537
    659537 Member Posts: 309
    yes you can alter index with "alter index" syntax to change.
    You can alter index for :-
    Rebuild or coalesce an existing index
    Deallocate unused space or allocate a new extent
    Specify parallel execution (or not) and alter the degree of parallelism
    Alter storage parameters or physical attributes
    Specify LOGGING or NOLOGGING
    Enable or disable key compression
    Mark the index unusable
    Start or stop the monitoring of index usage

    -----You cannot alter an index's column structure.---------

    For more details check in the documentation
    http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/indexes.htm#518
    · Share on TwitterShare on Facebook
  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge
    [ALTER INDEX|http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_1008.htm#SQLRF00805] is beautifully documented.

    Thanks,
    Karthick.
    · Share on TwitterShare on Facebook
This discussion has been closed.