Sybase 12.4.2 - Manual (Page 156)

Overview of indexes
136
The default column storage structure that Adaptive Server IQ creates for each
column is actually an index optimized for storing and projecting data.
Depending on the size of your database, the disk space available to you, and
the type of queries your users submit, you will almost certainly want to
supplement this default index with one or more of the Adaptive Server IQ
bitwise index types. You can choose from four column index types. The
column indexes you define are created as part of each individual table.
Besides the column indexes, Adaptive Server IQ also allows you to define join
indexes. Join indexes are optimized for joining related tables. You may want to
create a join index for any set of columns that your users commonly join to
resolve queries. Column indexes underlie any join indexes involving those
columns.
The first half of this chapter discusses column indexes. The second half of this
chapter discusses join indexes; see “Using join indexes” for details.
A default index that optimizes projections is created by Adaptive Server IQ
for all columns.
When a column is designated as either a PRIMARY KEY or UNIQUE , Adaptive
Server IQ creates a High_Group index for it automatically.
To achieve maximum query performance, however, you should choose one or
more additional index types for most columns that best represent the
cardinality and usage of column data:
• Low_Fast or LF A value-based bitmap for processing queries on low-
cardinality data (recommended for up to 1,000 distinct values, but can
support up to 10,000)
• High_Group or HG An enhanced b-tree index to process equality and
group by operations on high-cardinality data (recommended for more than
1,000 distinct values)
• High_Non_Group or HNG A non value-based bitmap index ideal for
most high-cardinality DSS operations involving ranges or aggregates
Select column indexes according to the type of data in the column and your
intended operations for the column data. In general, you can use any index or
combination of indexes on any column. However, there are some exceptions.