Sybase 12.4.2 manual Optimizing performance for ad hoc joins, Comparison to other indexes, 149

CHAPTER 4 Adaptive Server IQ Indexes

Table 4-7: HNG advantages/disadvantages

Advantages	Disadvantages
Due to compression algorithms used,	This index is not recommended for GROUP
disk space requirements can be	BY queries.
reduced without sacrificing
performance.
If the column has a high number of	Index not possible if uniqueness enforced.
unique values, this is the fastest index,
with few exceptions described below.
	Cannot use this index if data in your
	columns is FLOAT, REAL, DOUBLE, BIT, or
	VARCHAR > 255 bytes.

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

149