Sybase Adaptive Server IQ 12.4.2, Administration And Performance Manual

Page: 142 / 536

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