Sybase 12.4.2 ユーザーズマニュアル

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”

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

.

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. 

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