CHAPTER 4 Adaptive Server IQ Indexes

How Adaptive Server IQ uses indexes

Adding and dropping indexes

To take advantage of the High_Non_Group index types for columns with nonintegral numeric data, use the NUMERIC or DECIMAL data types, which support up to 254 digits to the left or right of the decimal point. Be aware that some index types are incompatible, and that creating indexes you don’t need wastes a lot of disk space. Read the sections that follow for details on how to select an index.

You may also want to define additional indexes on your columns for best performance. Adaptive Server IQ uses the fastest index available for the current query or join predicate. If you do not create the correct types of indexes for a column, Adaptive Server IQ can still resolve queries involving the column, but response may be slower than it would be with the correct index type(s).

If multiple indexes are defined on a particular column, Adaptive Server IQ builds all the indexes for that column from the same input data.

If you discover later that an additional index is needed, you can always add indexes. However, it is much faster to create all the appropriate indexes before you insert any data.

You can drop any optional index if you decide that you do not need it. See the DROP INDEX command in the Adaptive Server IQ Reference Manual for more information on dropping indexes. You cannot drop automatically created indexes using DROP INDEX. The only way to remove the default index is to use ALTER TABLE (or the Sybase Central Table Editor) to drop the column, or to drop the table. The only way to remove an automatically created HG index is by using ALTER TABLE (or the Sybase Central Table Editor) to drop the column or the PRIMARY KEY or UNIQUE constraint, or by dropping the table.

Benefits over traditional indexes

Adaptive Server IQ indexes offer these benefits over traditional indexing techniques:

Index sizes remain small. The entire database can be fully indexed and made available for ad hoc queries in the same space that would be needed to store the raw data. Most traditional databases need three times as much space.

Queries are resolved by efficiently combining and manipulating indexes on only the relevant columns. This avoids time-consuming table scans.

I/O is minimized, eliminating potential bottlenecks.

137

Page 157
Image 157
Sybase 12.4.2 manual Benefits over traditional indexes, Adaptive Server IQ Indexes, 137