Sybase IQ creates a High_Group index by default whenever you issue a CREATE INDEX statement without specifying an index type.
Sybase IQ automatically creates a High_Group index for any UNIQUE, FOREIGN KEY, or PRIMARY KEY constraint. For foreign keys of a single column, Sybase IQ creates a single column non-unique High_Group index. For multicolumn foreign keys, a non-unique composite High_Group index is implicitly created. The non-unique HG index allows duplicate values and optionally allows nulls. It provides the building block for referential integrity and can be used to improve query performance.
Sybase IQ allows the use of NULL in data values on a user created unique multicolumn HG index, if the column definition allows for NULL values and a constraint (primary key or unique) is not being enforced. For more details, see “Multicolumn indexes” in the “Notes” section of the CREATE INDEX statement in Chapter 1, “SQL Statements,” in Reference: Statements and Options.
Queries with joins on multiple columns or multicolumn group by clauses may improve performance because a non-unique composite High Group index provides more accurate cardinality estimates of joins and result sizes. It can also optimize pushdowns and subqueries.
