Sybase Adaptive Server IQ 12.4.2, Administration And Performance Manual

Page: 157 / 536

CHAPTER 4 Adaptive Server IQ Indexes
137
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.
How Adaptive Server
IQ uses indexes
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.
Adding and dropping
indexes
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.