Sybase Adaptive Server IQ 12.4.2, Administration And Performance Manual

Page: 168 / 536

Adaptive Server IQ index types
148
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.