In order to create a join index you must perform all of the following steps.
Create the tables involved in the join index, using the CREATE TABLE command, or using Sybase Central. These must be permanent tables; you cannot use a temporary table to create a join index.
Identify the join condition that relates specific pairs of columns in the underlying tables involved in any one join.
It is important to define a schema for your database, to clarify join conditions and other assumptions about the structure of your data. The schema should represent foreign key-primary key relationships, and follow other best practices of schema design. Columns related by foreign key must have matching data types, precision, and scale.
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.
Create a primary key for each column involved in a join.
Create column indexes for the tables being joined.
When Sybase 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. Multicolumn indexes on base tables are not replicated in join indexes created using those base tables.
Each column that is a foreign key, or a component of a foreign key, needs its own HG index. For multicolumn primary keys, if any column or subset of columns is used as a foreign key, you need to create an HG index explicitly on each such column. The HG index created automatically for the multicolumn primary key does not suffice for this purpose.
If your queries include joins on the multicolumn primary key, then you should define the multicolumn index, even if you are not using referential integrity. Creating the multicolumn index can help optimize these queries.
Load the data into the tables, using the LOAD TABLE command. You also can add data to existing tables using the INSERT INTO command.
You must insert into 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 indexed data.
Create the join index by issuing the CREATE JOIN INDEX command, or in Sybase Central with the New Join Index Wizard. You specify the join hierarchy as part of this step, as described in “Join hierarchy overview”.
Depending 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.
The index remains unavailable until all steps are complete. However, you can adjust the order of some steps, depending on the needs of your site:
You can combine steps 1 and 2 by defining relationships when you create the table.
You can load the data either before or after you create the join index. If you load the data into the underlying column indexes after you create the join index, you must perform the synchronization step.
