Filter
Top Products
CHAPTER 4 Adaptive Server IQ Indexes
143
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
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
Type of index
Estimated space
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
times larger
As the number of distinct values
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
times larger
Same as High_Group