CHAPTER 4 Adaptive Server IQ Indexes

These estimates are generally valid; however, other factors can take precedence:

For range predicates, the number of unique values is a more important factor.

With the set functions COUNT, COUNT DISTINCT, SUM, MIN, MAX, and AVG, in order to use any index other than the default, the entire query must be resolvable using a single table or join index.

BIT data, and VARCHAR data greater than 255 bytes, can only be used in the default index.

Indexing criteria: disk space usage

The following table provides estimates of the amount of space each index uses compared to the amount of column data from the source database or flat file.

Table 4-3: Index disk space usage

 

 

Estimated space

 

Type of index

versus raw data

Comments

 

 

 

Default

Smaller than or equal to

If the number of distinct values is

 

 

less than 255, this index uses

 

 

significantly less space than the raw

 

 

data

 

 

 

High_Group

Smaller than up to 2

As the number of distinct values

 

times larger

decreases (that is, the number of

 

 

entries per group increases), the

 

 

space used decreases in proportion

 

 

to the size of the raw data

 

 

 

High_Non_Group

Smaller than or equal to

Smaller than the raw data in most

 

 

cases

 

 

 

Low_Fast

Smaller than up to 2

Same as High_Group

 

times larger

 

 

 

 

For LF and HG indexes, the index size depends on the number of unique values.

The more unique values, the more space the index takes.

Data types in the index

Only the default index supports the following data types:

BIT data

143

Page 163
Image 163
Sybase 12.4.2 manual Indexing criteria disk space usage, Data types in the index, 143, Index disk space usage