Sybase 12.4.2 User Manual

CHAPTER 4    Adaptive Server IQ Indexes

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. 

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.

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 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.