CHAPTER 4 Adaptive Server IQ Indexes

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

 

Number of Unique Values

Recommended Index Type

 

 

Below 1,000

LF

 

 

1000 and over

HG and/or HNG

 

 

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?

141

Page 161
Image 161
Sybase 12.4.2 manual Number of unique values in the index, Types of queries, 141