In read-only environments, or for any table that does not have frequent inserts, it might be beneficial to define a clustered index. (You can have only one clustered index per table.) Specifying the CLUSTER option on the CREATE INDEX command gives you the option to later reorganize (re-order) the table's records into the same sequence as the related clustered index. For example:
CREATE INDEX pnoindex ON part (pno) CLUSTER
To reorganize the table's records into the same sequence as the clustered index's Record IDs, you must use the SQL COMPACT command with the CLUSTER BY INDEX option. For example:
COMPACT TABLE part CLUSTER BY INDEX pnoindex
Determining how often a table needs to be reorganized depends on how often records are added (inserted). The server leaves records in place when an update is issued (maintaining the order of records), but always inserts new records at the end of a table. A table that has many inserts would therefore need a reorganization more often than one that has few inserts.
If an update is made to the clustering key, the order of records in the table may be compromised. Use COMPACT to correct this situation.
See the CREATE INDEX and COMPACT command descriptions in the SQL Reference for more information about clustered indexes.