Sybase Adaptive Server IQ 12.4.2, Administration And Performance Manual

Page: 171 / 536

CHAPTER 4 Adaptive Server IQ Indexes
151
Adding column indexes after inserting data
When you create an additional column index, the
CREATE INDEX
command
creates the new index as part of the individual table and as part of any join
indexes that include the column.
If the existing column indexes in the individual table already contain data, the
CREATE INDEX
statement also inserts data into the new index from an existing
index. This ensures data integrity among all the column indexes for columns
within an individual table. Data is also inserted and synchronized
automatically when you add an index to previously loaded tables that are part
of a join index. For information on synchronization, see “Synchronizing join
indexes”.
This capability is useful if you discover that a column needs an additional index
after you have already inserted data. This allows you to add the index without
having to start over.
Note Inserting data from an existing index can be slow. It is always faster to
create all the appropriate indexes before you insert data, then insert into all of
them at once, with either the
LOAD TABLE
or
INSERT
statement.
Using join indexes
If you know that certain tables in the same database will typically be joined in
a consistent way, you should create a join index for those tables. When you
create a join index, Adaptive Server IQ produces a new internal structure that
relates table columns. It represents two or more tables, including the inner, left
outer, and right outer rows.
Join indexes improve query performance
Join indexes provide better query performance than when table joins are first
defined at query time (ad hoc joins). However, they require more space and
time to load. To load a join index, you must first load the underlying tables, and
then load the join index.