Filter
Top Products
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.