Sybase 12.4.2 manual Steps in creating a join index, 157

CHAPTER 4 Adaptive Server IQ Indexes

Of course, this approach requires more disk space to build an additional join index and more index building time (not to mention increased maintenance). In the case of a subset join index, the additional join index repeats a subset of the information already in the full join index. You must decide whether the query speed or disk space usage of your application is more important for this particular join relationship.

Steps in creating a join index

In order to create a join index you must perform all of the following steps:

1Create the tables involved in the join index, using the CREATE TABLE command, or using Sybase Central.

2Identify the join condition that relates specific pairs of columns in the underlying tables involved in any one join. Where the relationship is based on a key join, you must define join conditions as referential integrity

constraints—primary and foreign key declarations—in the CREATE TABLE commands in step 1, or in ALTER TABLE commands.

3Create column indexes for the tables being joined.

When Adaptive Server IQ creates a join index between tables, the IQ column index types and data types already defined on the single tables are used in the join index.

4Load the data into the tables, using the LOAD TABLE command. You also can add data to existing tables using the INSERT INTO command.

Note You must insert into the column index of each table in the join index as a single-table insert, rather than into the join index itself. This approach conforms to ANSI rules for prejoined data.

5Create the join index by issuing the CREATE JOIN INDEX command, or in Sybase Central with the Add JoinIndex Wizard. You specify the join hierarchy as part of this step, as described in “Join hierarchy overview”.

Note If data exists in the join tables, a synchronize occurs automatically.

6Depending on the order in which you perform these steps, you may need to synchronize the tables in the join index, as described below. If data exists in the join tables, synchronization occurs automatically.

157