Sybase Adaptive Server IQ 12.4.2, Administration And Performance Manual

Page: 161 / 536

CHAPTER 4 Adaptive Server IQ Indexes
141
Number of unique values in the index
Adaptive Server IQ indexes are optimized according to the number of unique
(distinct) values they include. When this number reaches certain levels, choose
indexes according to the recommendations in Table 4-1.
Table 4-1: Consideration order
Columns for which you specify
IQ UNIQUE 65536
or less are automatically
placed in a form of the default index that is optimized for reduced storage, and
improved performance for certain types of queries.
Here are some examples of columns with different numbers of unique values:
• Columns that hold marital status will have just a few unique values (single,
married, NULL)
• Columns that hold state or province names will have fewer than 100
unique values
• Columns that hold date data probably have more than 100 but fewer than
65536 unique values
• Columns that hold account numbers or social security numbers may have
thousands or millions of unique numbers
Types of queries
You should know in advance how data in the columns will generally be
queried. For example:
• Will the column be part of a join predicate?
• If the column has a high number of unique values, will the column be used
in a
GROUP BY
clause, be the argument of a
COUNT DISTINCT
, and/or be
in the
SELECT DISTINCT
projection?
Number of Unique Values Recommended Index Type
Below 1,000 LF
1000 and over
HG
and/or
HNG