Storage Structure Specification
Changing the storage structure of a table or index is often done to improve performance of access to the table. For example, change the structure of a table to heap before performing a bulk copy into the table to improve the performance of copy.
The storage_structure parameter must be one of the following table storage structures:
isam
Indexed sequential access method structure, duplicate rows allowed unless the with noduplicates clause is specified when the table is created.
hash
Random hash storage structure, duplicate rows allowed unless the with noduplicates clause is specified when the table is created
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.
You cannot modify an index to heap or heapsort.
The DBMS Server uses existing data to build the index (for isam 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 you specify unique for 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.
You cannot specify unique for heap or heapsort tables.
The optional on clause determines the table's primary keys. You can only specify this clause 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 you omit the on clause when modifying to isam, hash, or btree, the table is keyed, by default, on the first column. When you modify a table to heap, you must omit the on clause.
When you modify a table to heapsort, you can specify the sort order as asc (ascending) or desc (descending). The default is ascending. When sorting, the DBMS Server considers nulls greater than any non-null value.