Steps in creating a join index – Sybase 12.4.2 User Manual

Page 177

CHAPTER 4 Adaptive Server IQ Indexes

157

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:

Create the tables involved in the join index, using the

CREATE TABLE

command, or using Sybase Central.

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

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

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

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

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.