Sybase 12.4.2 manual Adding column indexes after inserting data, Using join indexes, 151

CHAPTER 4 Adaptive Server IQ Indexes

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.

151