Overview of indexes

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.

136

Page 156
Image 156
Sybase 12.4.2 manual Overview of indexes, 136