TO CLUSTER Option

The TO CLUSTER option causes the database server to reorder the rows of the physical table according to the order of index-key values.

Clustering an index rebuilds the table in a different location within the same dbspace. When you run the ALTER INDEX statement with the TO CLUSTER keywords, all of the extents associated with the previous version of the table are released. The resulting newly-built version of the table has no empty extents.

For an ascending index, TO CLUSTER puts rows in lowest-to-highest order. For a descending index, the rows are reordered in highest-to-lowest order.

When you reorder, the entire file is rewritten. This process can take a long time, and it requires sufficient disk space to maintain two copies of the table.

While a table is clustering, it is locked IN EXCLUSIVE MODE. When another process is using the table to which the index name belongs, the database server cannot execute the ALTER INDEX statement with the TO CLUSTER keywords; it returns an error unless lock mode is set to WAIT. (When lock mode is set to WAIT, the database server retries the ALTER INDEX statement.)

Over time, if you modify the table, you can expect the benefit of an earlier cluster to disappear because rows are added in space-available order, not sequentially. You can recluster the table to regain performance by issuing another ALTER INDEX TO CLUSTER statement on the clustered index. You do not need to drop a clustered index before you issue another ALTER INDEX TO CLUSTER statement on a currently clustered index.

Example of clustering an index

The following example shows how you can use the ALTER INDEX TO CLUSTER statement to physically order the rows in the orders table. The CREATE INDEX statement creates an index on the customer_num column of the table. Then the ALTER INDEX statement causes the physical reordering of the existing rows within the orders table: following

CREATE INDEX ix_cust ON orders (customer_num); 
ALTER INDEX ix_cust TO CLUSTER;