![background image](http://html1.mh-extra.com/html/sybase/adaptive-server-iq-12-4-2/adaptive-server-iq-12-4-2_administration-and-performance-manual_1416503162.webp)
Choosing an index type
142
Often, the type of data in a column gives a good indication how the column will
be used. For example, a date column will probably be used for range searches
in
WHERE
clauses, and a column that contains prices or sales amounts will
probably be used in the projection as an argument for aggregate functions
(
SUM
,
AVG
, and so on).
Note
Adaptive Server IQ can still resolve queries involving a column indexed
with the wrong index type, although it may not do so as efficiently.
This table shows recommended index types based on the query. The index that
is usually fastest for each query is listed first, the slowest last. These
recommendations should not be your only criteria for picking an index type.
You should also consider the number of unique values and disk space. See the
other tables in this section.
Table 4-2: Query type/index
Note
While
HNG
is recommended, in certain cases
LF
or
HG
is faster, and is
often used in place of
HNG
.
HNG
tends to give consistent performance, while
the performance of
LF
or
HG
with ranges depends on the size of the range
selected.
Type of Query Usage
Recommended Index Type
In a
SELECT
projection list
Default
In calculation expressions such as
SUM
(A+B)
Default
As
AVG/SUM
argument
High_Non_Group, Low_Fast,
High_Group
, Default
As
MIN/MAX
argument
LF, HG, HNG
As
COUNT
argument
LF, HG
As
COUNT DISTINCT, SELECT DISTINCT
or
GROUP BY
argument.
LF
, Default
If field does not allow duplicates
HG
Columns used in ad hoc join
Default,
HG, LF
,
Columns used in a join index
HG, LF
As
LIKE
argument in a
WHERE
clause
Default
As
IN
argument
HG, LF
In equality or inequality (=, <>)
HG, LF
In range predicate in
WHERE
clause (>, <,
>=, <=,
BETWEEN
LF
or
HNG
Summary of Contents for Adaptive Server IQ 12.4.2
Page 1: ...Administration and Performance Guide Adaptive Server IQ 12 4 2 ...
Page 16: ...xvi ...
Page 20: ...Related documents xx ...
Page 40: ...Compatibility with earlier versions 20 ...
Page 118: ...Troubleshooting startup shutdown and connections 98 ...
Page 248: ...Importing data by replication 228 ...
Page 306: ...Integrity rules in the system tables 286 ...
Page 334: ...Cursors in transactions 314 ...
Page 396: ...Users and permissions in the system tables 376 ...
Page 438: ...Determining your data backup and recovery strategy 418 ...
Page 484: ...Network performance 464 ...
Page 500: ...System utilities to monitor CPU use 480 ...
Page 514: ...Characteristics of Open Client and jConnect connections 494 ...
Page 536: ...Index 516 ...