Sybase 12.4.2 HighNonGroup HNG index type, Advantages and disadvantages of HighNonGroup, 148

Adaptive Server IQ index types

However, because multi-column primary keys are always unenforced, the automatically created High_Group index for a multi-column primary key is a phantom index: it includes all of the key columns, but does not contain any data. This structure is used for query optimization, but not for resolving queries. You need to create explicitly an HG (or LF) index on any multi-column primary key columns that will be used in a join predicate.

The High_Non_Group (HNG) index type

Add an HNG index when you need to do range searches.

An HNG index requires approximately three times less disk space than an HG index requires. On that basis alone, if you do not need to do group operations, use an HNG index instead of a HG index.

Conversely, if you know you are going to do queries that a HG index handles more efficiently, or if the column is part of a join and/or you want to enforce uniqueness, use a HG index.

Note Using the HNG index in place of a HG index may seriously degrade performance of complex ad-hoc queries joining four or more tables. If query performance is important for such queries in your application, choose HG instead of HNG.

Recommended use

Use an HNG index when:

•The number of unique values is high (greater than 1000)

•You don't need to do GROUP BY on the column

Advantages and disadvantages of High_Non_Group

See the following table for advantages and disadvantages of using a

High_Non_Group index.

148