CREATE_HG_AND_FORCE_PHYSICAL

Allowed Values

ON/OFF

Default

ON

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_AND_FORCE_PHYSICAL_DELETE determines whether delete operation physically removes rows from an HG immediately or defers the removal to a point later in the load:

Setting CREATE_HG_AND_FORCE_PHYSICAL_DELETE='ON' (default) instructs SAP Sybase IQ to perform a physical delete, which increases the performance of some queries (link in .. list and ordered projection, for example), but can cause delete queries on tables with tiered HG indexes to run more slowly.
Setting CREATE_HG_AND_FORCE_PHYSICAL_DELETE='OFF' instructs SAP Sybase IQ to perform a virtual or deferred delete, which improves delete query performance, but can impact queries on tables with tiered HG indexes.

Set CREATE_HG_AND_FORCE_PHYSICAL_DELETE before creating a tiered HG column index. It does not affect preexisting HG indexes. It has no effect on sp_iqrebuildindex. This option persists through the life of the tiered HG index, and cannot be changed or modified unless the index is dropped and the option toggled before recreating the index (sp_iqrebuildindex cannot modify the status of the index).

Note: sp_iqrebuildindex output includes a Force Physical Delete column that identifies the status of this option.