Sybase 12.4.2 - Manual (Page 161)

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