Changing the storage structure of a table or index is typically done to improve performance when accessing the table. For example, to improve the performance of COPY, change the structure of a table to heap before performing a bulk copy into the table.

The storage_structure parameter must be one of the following:

An index cannot be modified to HEAP, HEAPSORT, or RTREE.

The DBMS Server uses existing data to build the index (for isam and btree tables), calculate hash values (for hash tables) or for sorting (heapsort tables).

To optimize the storage structure of heavily used tables (tables containing data that is frequently added to, changed, or deleted), modify those tables periodically.

The optional keyword UNIQUE requires each key value in the restructured table to be unique. (The key value is the concatenation of all key columns in a row.) If UNIQUE is specified on a table that contains non-unique keys, the DBMS Server returns an error and does not change the table's storage structure. For the purposes of determining uniqueness, a null is considered to be equal to another null. Use UNIQUE only with isam, hash, and btree tables.

The optional ON clause determines the table's storage structure keys. This clause can only be specified when modifying to one of the following storage structures: isam, hash, heapsort, or btree. When the table is sorted after modification, the first column specified in this clause is the most significant key, and each successive specified column is the next most significant key.

If the ON clause is omitted when modifying to isam, hash, or btree, the table is keyed, by default, on the first column. When a table is modified to heap, the ON clause must be omitted.

When modifying a table to heapsort, specify the sort order as ASC (ascending) or DESC (descending). The default is ASC. When sorting, the DBMS Server considers nulls greater than any non‑null value.

In general, any MODIFY...TO storage_structure ... of a table or index assigned to a raw location must include WITH LOCATION=(...) syntax to move the table or index to another set of locations because modify semantics involve a create, rename, and delete file, which works efficiently for cooked locations, but does not adapt to raw locations.

If UNIQUE is used with a partitioned table, the new storage structure must be compatible with the table's partitioning scheme. This means that given a value for the unique storage structure key columns, it must be possible to determine that a row with that key will be in one particular physical partition. In practice, this rule means that the partitioning scheme columns must be the same as (or a subset of) the storage structure key columns. It also means that unique cannot be used with AUTOMATIC partitioning. A MODIFY TO UNIQUE that violates the partitioning rule will be rejected with an error.