Sybase Adaptive Server IQ 12.4.2, Administration And Performance Manual

Page: 155 / 536

135
C H A P T E R 4
Adaptive Server IQ Indexes
About this chapter This chapter describes the Adaptive Server IQ index types. It explains
how you create an index, and provides information to help you decide
what index types are best suited for the way you use the data in your
database. It also includes performance and resource issues related to
indexing.
Overview of indexes
Indexes are used to improve data retrieval performance. Traditional
indexes use a B-tree index strategy to point to the data records. That
strategy is valuable only if many unique data values are used to filter down
to a very small set of records, as with columns of order numbers or
customer names, as you would encounter in a transaction processing
system.
Adaptive Server IQ indexes actually represent and store the data so that
the data can be used for processing queries. This strategy is designed for
the data warehousing environment, in which queries typically examine
enormous numbers of records, often with relatively few unique values,
and in which aggregate results are commonly required.
Adaptive Server IQ index types
When you load data into a table, Adaptive Server IQ stores data by column
rather than by row, for each column in the table. The column orientation
gives IQ indexes important advantages over traditional row-based
indexing. Column storage structures your data according to the attributes
you are interested in tracking. In a data warehousing environment, usually
you want to look at specific attributes of thousands or millions of rows of
data, rather than complete, single rows of data that typically are the focus
in transaction processing. Column storage optimizes your ability to
perform selections or calculations on the attributes you care about.