Working with tables
122
If you use the ALTER TABLE command to add a UNIQUE column to an existing
table, or to designate an existing column as
UNIQUE, an HG index is created
automatically.
For complete information on IQ indexing, see Chapter 4, “Adaptive Server IQ
Indexes”
Optimizing storage and query performance
When you create a permanent table in an IQ database, Adaptive Server IQ
automatically stores it in a default index that facilitates a type of query called
a projection.
Adaptive Server IQ optimizes this structure for query performance and storage
requirements, based on these factors:
•The
IQ UNIQUE option of CREATE TABLE.
• The data type of the column and its width
•The
IQ PAGE SIZE option of CREATE DATABASE
See the following table for implications of IQ UNIQUE.
Table 3-3: Effect of IQ UNIQUE
Difference between
UNIQUE and IQ
UNIQUE
IQ UNIQUE (
count
) gives an approximation of the number of distinct values that
can be in a given column. Each distinct value can appear many times. For
example, in the
employee table, a limited set of distinct values could appear in
the
state column, but each of those values could appear in many rows.
IQ UNIQUE 256 or
less
IQ UNIQUE 65536 or
less
IQ UNIQUE unspecified
or greater than 65536
Storage optimized for
small number of unique
values
Storage optimized for
medium number of
unique values
Storage optimized for large
number of unique values
Faster query
performance, less main
IQ Store space required
Faster query performance,
less main IQ Store space
required
Queries may be slower
Need a small amount of
extra cache for IQ
Temporary Store
Need extra cache for IQ
Temporary Store. The
amount depends on the
number of unique values
and the data type.
No extra cache needed
Loads may be slower Loads may be slower Loads are faster