Informix Guide to SQL: Syntax SQL Statements

Use the ALTER INDEX statement to put the data in a table in the order of an existing index or to release an index from the clustering attribute.

The ALTER INDEX statement works only on indexes that are created with the CREATE INDEX statement; it does not affect constraints that are created with the CREATE TABLE statement.

You cannot alter the index of a temporary table.

The TO CLUSTER option causes the rows in the physical table to reorder in the indexed order.

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

CREATE  INDEX  ix_cust  ON  orders  (customer_num);



ALTER  INDEX  ix_cust  TO  CLUSTER; 

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, the table 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 option; 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.

If you are using Enterprise Decision Server, you cannot use the CLUSTER option on STANDARD tables.

The NOT option drops the cluster attribute on the index name without affecting the physical table. Because only one clustered index per table can exist, you must use the NOT option to release the cluster attribute from one index before you assign it to another. The following statements illustrate how to remove clustering from one index and how a second index physically reclusters the table:

CREATE  UNIQUE  INDEX  ix_ord

	ON  orders  (order_num);



CREATE  CLUSTER  INDEX  ix_cust

 	ON  orders  (customer_num); 

.

.

.

 

ALTER  INDEX  ix_cust  TO  NOT  CLUSTER; 

 

ALTER  INDEX  ix_ord  TO  CLUSTER; 

The first two statements create indexes for the orders table and cluster the physical table in ascending order on the customer_num column. The last two statements recluster the physical table in ascending order on the order_num column.

Use the lock modes to specify the locking granularity of the index.

When you use the coarse-lock mode, index-level locks are acquired on the index instead of item-level or page-level locks. This mode reduces the number of lock calls on an index.

Use the coarse-lock mode when you know the index is not going to change, that is, when read-only operations are performed on the index.

Use the coarse-lock mode to have the database server place item-level or page-level locks on the index as necessary. Use this mode when the index gets updated frequently.

When the database server executes the command to change the lock mode to coarse, it acquires an exclusive lock on the table for the duration of the command. Any transactions that are currently using a lock of finer granularity must complete before the database server switches to the coarse-lock mode.

Related statements: CREATE INDEX and CREATE TABLE

For a discussion of the performance implications of clustered indexes, see your Performance Guide.