Sybase Adaptive Server IQ 12.4.2, Administration And Performance Manual

Page: 177 / 536

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:
1 Create the tables involved in the join index, using the
CREATE TABLE
command, or using Sybase Central.
2 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.
3 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.
4 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.
5 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.
6 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.