CREATE_HG_WITH_EXACT

Determines whether the database engine creates tiered HG or single-tiered HG indexes.

Allowed Values

ON, OFF

Default

OFF

Scope

Option can be set at the database (PUBLIC) or user level. When set at the database level, the value becomes the default for any new user, but has no impact on existing users. When set at the user level, overrides the PUBLIC value for that user only. No system privilege is required to set option for self. System privilege is required to set at database level or at user level for any user other than self.

Requires the SET ANY PUBLIC OPTION system privilege to set this option. Can be set temporary for an individual connection or for the PUBLIC role. Takes effect immediately.

Remarks

CREATE_HG_WITH_EXACT_DISTINCTS determines whether the database engine creates HG indexes as single HG or tiered HG:

If CREATE_HG_WITH_EXACT_DISTINCTS='ON', all subsequent HG indexes explicitly created with a CREATE INDEX command or implicitly creating or altering a table with a PRIMARY KEY or a FOREIGN KEY declaration, will be non-tiered HG indexes.
If CREATE_HG_WITH_EXACT_DISTINCTS='OFF', all subsequent HG indexes explicitly created with a CREATE INDEX command or implicitly creating or altering a table with a PRIMARY KEY or a FOREIGN KEY declaration, will be tiered HG.

This option is ON by default in all newly created 16.0 databases, and all 16.0 database upgraded from SAP Sybase IQ 15.x. To take advantage of the new tiered structure, set this option to OFF. Use sp_iqrebuildindex to convert non-tiered HG indexes to tiered HG and vice-versa.