MINIMIZE_STORAGE option

Function

Minimize use of disk space for newly created columns.

Allowed Values

ON, OFF

Default

OFF

Scope

Can be set for the PUBLIC group or for temporary use. DBA authority is not required to set the option. This option takes effect immediately.

Description

When MINIMIZE_STORAGE is ON, IQ optimizes storage for new columns by using as little as one byte of disk space per row wherever appropriate. By default, this option is OFF for the PUBLIC group, and the specialized storage optimization does not occur for all newly created columns; when MINIMIZE_STORAGE is OFF for the PUBLIC group but ON as a temporary user option, one-byte storage is used for new columns created by that user ID.

MINIMIZE_STORAGE=ON is equivalent to placing an IQ UNIQUE 255 clause on every new column, with the exception of certain data types that are by nature too wide for one-byte storage. When MINIMIZE_STORAGE is ON, there is no need to specify IQ UNIQUE except for columns with more than 65536 unique values.

NoteAn IQ UNIQUE value greater than 65536 can allow the creation of 3-byte indexes, whereas previously such values were used to prevent it with MINIMIZE_STORAGE ON. If you want to prevent the specialized storage optimization with MINIMIZE_STORAGE ON, give IQ UNIQUE a constraint value greater than 16777216.

When the ratio of main memory to the number of columns is large, turning MINIMIZE_STORAGE ON is beneficial. Otherwise, storage of new columns generally benefits from turning this option OFF.

Specifying IQ UNIQUE explicitly in CREATE TABLE or ALTER TABLE ADD COLUMN overrides the MINIMIZE_STORAGE option for that column.

See also

“FP_LOOKUP_SIZE option” and “INDEX_ADVISOR option”

“The Fast Projection (FP) default index type” in Chapter 6, “Using Sybase IQ Indexes” in System Administration Guide: Volume 1

Chapter 5, “Working with Database Objects” and Chapter 6, “Using Sybase IQ Indexes” (particularly “FP(3) index”) in System Administration Guide: Volume 1