SQL Reference Guide > SQL Reference Guide > SQL Statements > MODIFY > Storage Structure Specification
Was this helpful?
Storage Structure Specification
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:
ISAM
Indexed Sequential Access Method structure, duplicate rows allowed unless the WITH NODUPLICATES clause is specified when the table is created. Maximum key width is 1003 bytes.
HASH
Random hash storage structure, duplicate rows allowed unless the WITH NODUPLICATES clause is specified when the table is created. Maximum key width is 32000 bytes.
HEAP
Unkeyed and unstructured, duplicated rows allowed, even if the WITH NODUPLICATES clause is specified when the table is created.
HEAPSORT
Heap with rows sorted and duplicate rows allowed unless the WITH NODUPLICATES clause is specified when the table is created (sort order not retained if rows are added or replaced).
BTREE
Dynamic tree-structured organization with duplicate rows allowed unless the WITH NODUPLICATES clause is specified when the table is created. Maximum key width depends on the page_size:
Page Size
Maximum Key Width in Bytes
2K
440
4K
500
8K
1000
16K
2000
32K
2000
64K
2000
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.
Note:  It is still possible to enforce an arbitrary uniqueness constraint on a partitioned table, regardless of the partitioning scheme, by adding a UNIQUE or PRIMARY KEY constraint, or a unique secondary index, to the table.
Last modified date: 01/30/2023