Sybase 12.4.2 Optimizing storage and query performance, Data type of the column and its width

Working with tables

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

Difference between UNIQUE and IQ UNIQUE

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
IQ UNIQUE 256 or	IQ UNIQUE 65536 or	IQ UNIQUE unspecified
less	less	or greater than 65536
Storage optimized for	Storage optimized for	Storage optimized for large
small number of unique	medium number of	number of unique values
values	unique values
Faster query	Faster query performance,	Queries may be slower
performance, less main	less main IQ Store space
IQ Store space required	required
Need a small amount of	Need extra cache for IQ	No extra cache needed
extra cache for IQ	Temporary Store. The
Temporary Store	amount depends on the
	number of unique values
	and the data type.
Loads may be slower	Loads may be slower	Loads are faster

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.

122