Altering the clustering of an index

You can use the ALTER INDEX SQL statement to change the clustering index for a table.

To change the clustering option of an index:

1. Issue the ALTER INDEX statement.
2. Specify the clustering option.
   Restriction: You can only specify CLUSTER if there is not already another clustering index. In addition, an index on a table that is organized by hash cannot be altered to a clustering index.
   • CLUSTER indicates that the index is to be used as the clustering index of the table. The change takes effect immediately. Any subsequently inserted rows use the new clustering index. Existing data remains clustered by the previous clustering index until the table space is reorganized.
   • NOT CLUSTER indicates that the index is not to be used as the clustering index of the table. However, if the index was previously defined as the clustering index, it continues to be used as the clustering index until you explicitly specify CLUSTER for a different index.

     If you specify NOT CLUSTER for an index that is not a clustering index, that specification is ignored.

3. Commit the alter procedure.