Optimizing storage and query performance

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 (CREATE TABLE or plug-in Column Properties page)
The MINIMIZE_STORAGE option (SET OPTION or plug-in Database Options dialog)
The data type of the column and its width
The IQ PAGE SIZE option (CREATE DATABASE or plug-in Create Database wizard)

See the following table for implications of IQ UNIQUE.

**Table 5-7: Effect of IQ UNIQUE**
IQ UNIQUE 256 or less	IQ UNIQUE 65536 or less	IQ UNIQUE 16777216 or less	IQ UNIQUE unspecified or greater than 16777216
Storage optimized for small number of unique values	Storage optimized for medium number of unique values	Storage optimized for 3-byte FP indexes	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	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.	Need significant 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 may be slower	Loads are faster

Effect of MINIMIZE_STORAGE option

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 Reference: Statements and Options.

Indexes and IQ UNIQUE

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 7, “System Procedures,” in Reference: Building Blocks, Tables, and Procedures.

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.

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.