When you create a permanent table in an IQ database, Sybase IQ automatically stores it in a default index that facilitates a type of query called a projection.
Sybase IQ optimizes this structure for query performance and storage requirements, based on these factors:
The IQ UNIQUE option of CREATE TABLE
The MINIMIZE_STORAGE database option
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.
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 for loads |
Need extra cache for IQ Temporary Store for loads. The amount depends on the number of unique values and the data type. |
No extra cache needed for loads |
Loads may be slower if you have numerous columns with IQ UNIQUE <256 |
Loads may be slower |
Loads are faster |
When MINIMIZE_STORAGE is ON, it is equivalent to specifying IQ UNIQUE 255 for all new columns. MINIMIZE_STORAGE defaults to OFF. For details, see “MINIMIZE_STORAGE option” in Sybase IQ Reference Manual.
If you estimate IQ UNIQUE incorrectly,
there is no penalty for loads; the Optimizer simply uses the next
larger index. For queries, if you estimate IQ UNIQUE incorrectly
and you have an HG, LF, or
storage-optimized default index, the Optimizer ignores the IQ
UNIQUE value and uses the actual number of values in the
index. If you do not have one of these indexes and your estimate
is wrong by a significant amount (for example, if you specify IQ UNIQUE
1000000 when the actual number of unique values
is 12 million), query performance may suffer.
To change the value of IQ UNIQUE for an existing index, run the sp_iqrebuildindex procedure. For details, see Chapter 9, “System Procedures” in Sybase IQ Reference Manual.
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.
By contrast, when you specify UNIQUE or PRIMARY KEY, each value can occur only once in that column. For example, in the employee table, each value of ss_number, the employee's social security number, can occur just once throughout that column. This uniqueness extends to NULL values. Thus, a column specified as UNIQUE must also have the constraint NOT NULL.
