Determines whether the database engine creates tiered HG or single-tiered HG
indexes.
Scope
Option can be set at the
database (PUBLIC) or user level. At the database level, the value becomes the
default for any new user, but has no impact on existing users. 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 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.