The storage structure of a base table, when created, 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 and the usual secondary index is not built.

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.

This feature improves the initial performance of the table. Previously, all new tables were created with a heap structure, by default.

This feature is enabled or disabled in a given DBMS Server by setting the configuration parameter table_auto_structure to ON or OFF. The default is ON.