Clustered Indices

When a clustered index is created the table is rebuilt so that the rows in the new table are in the same physical order as they occur in the clustered index. This can improve the speed of access to the data in that table via that index. However, it is important to note that the user cannot dictate whether any index will be used in any given query - the database engine optimises the query and will use the index if it thinks there will be any advantage to doing so. Of necessity, there can be only one clustered index on a table.

[Note: the latter engines have optimiser directives that will force the optimiser to use a partiuclar index. Directives must be enabled in the ONCONFIG. As a general rule optmiser directives are more trouble than they are worth]

For example, to create a clustered index on the customer table, use:

CREATE CLUSTER INDEX cust_name ON customer (surname, firstname, title);

To change an existing index to unclustered, use:

ALTER INDEX cust_name TO NOT CLUSTER;

To change it back to clustered, use:

ALTER INDEX cust_name TO CLUSTER;

Altering an index to unclustered is a quick operation; changing it to clustered means rebuilding the table.

It is important to note that a clustered index is created once. Immediately afterwards, the physical order of the records corresponds to the logical order of the records. However, as more records are inserted and deleted, the order becomes increasingly scrambled.

The process of clustering an index may be used to housekeep a table - that is, remove fragmentation table and disorder of the rows due to deletions, insertions and updates. Additionally, for IDS engines the VARCHAR columns will be updated. The process consists of the following steps:

Drop all but one of the indexes on the table. The logical index to retain is that based on the Primary Key.

Execute the following two statements:

ALTER INDEX index_name TO CLUSTER;

ALTER INDEX inder_name TO NOT CLUSTER;

Any other indexes required on the table may be recreated.

Note: Creating a clustered index on a large table makes the transaction log grow very fast.

Note: Because clustering updates the sysindices table entry the cluster operation will be passed out as sql when using dbexport. This can make dbexports take considerably longer.