Was this helpful?
Default Storage Structure of New Tables
The default storage structure of a newly created base table is determined by the setting of the configuration parameter table_auto_structure (or using WITH [NO]AUTOSTRUCT in CREATE TABLE) in combination with the presence of constraint definitions in the CREATE TABLE statement.
When table_auto_structure is ON, the storage structure of a base table is automatically determined based on the syntax used for the CREATE TABLE statement. If the CREATE TABLE statement includes at least a primary key, unique constraint, or referential (foreign key) constraint, the base table structure is set to B-tree on the constrained columns and the usual secondary index is not built for the constraint.
If the table definition includes more than one constraint, it chooses the primary key constraint over a unique constraint, and the first unique constraint over any referential constraint. For primary key or unique constraints, it also adds the UNIQUE_SCOPE=STATEMENT attribute to the base table structure. A dependency is added between the constraint and the base table structure so that the constraint must be explicitly dropped and re-added if the base table structure is modified.
When table_auto_structure is OFF or if there are no accompanying constraint definitions, the default storage structure of all new base tables is heap.
Last modified date: 01/30/2023