Sybase Adaptive Server IQ 12.4.2, Administration And Performance Manual

Page: 169 / 536

CHAPTER 4 Adaptive Server IQ Indexes
149
Table 4-7: HNG advantages/disadvantages
Comparison to other indexes
•
HNG
needs less disk space than
HG
but can't perform
GROUP BY
efficiently.
• In choosing between
LF
and
HNG
, the determining factor is the number of
unique values. Use
HNG
when the number of unique values is greater than
1000.
Additional indexes
The High_Group index is also appropriate for an
HNG
column.
Optimizing performance for ad hoc joins
To gain the fastest processing of ad hoc joins, create a Low_Fast or
High_Group index on all columns that may be referenced in:
•
WHERE
clauses of ad hoc join queries
•
HAVING
clause conditions of ad hoc join queries outside of aggregate
functions
For example:
SELECT n_name, sum(l_extendedprice*(1-l_discount))
AS revenue
FROM customer, orders, lineitem, supplier,
nation, region
WHERE c_custkey = o_custkey
AND o_orderkey = l_orderkey
Advantages Disadvantages
Due to compression algorithms used,
disk space requirements can be
reduced without sacrificing
performance. This index is not recommended for
GROUP
BY queries.
If the column has a high number of
unique values, this is the fastest index,
with few exceptions described below. Index not possible if uniqueness enforced.
Cannot use this index if data in your
columns is
FLOAT , REAL , DOUBLE , BIT , or
VARCHAR
> 255 bytes.