Sybase 12.4.2 User Manual
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.
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.
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.
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.
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:
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)
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)
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.
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.