Sybase Adaptive Server IQ 12.4.2, Administration And Performance Manual

Page: 179 / 536

CHAPTER 4 Adaptive Server IQ Indexes
159
Performance hints for synchronization
Synchronization can be time-consuming. To improve performance, try these
suggestions:
• Schedule synchronization during off-peak hours.
• Synchronize join indexes individually rather than all at once.
• Synchronize after executing an entire set of insertions and deletions. It is
not a good idea to synchronize after every insertion or deletion, as the time
it takes to update a join index depends significantly on the order of the
updates to the tables. Synchronizing sets of updates allows Adaptive
Server IQ to pick the optimal order for applying the table changes to the
join index.
Defining join relationships between tables
When you create a join index, you must specify the relationship between each
related pair in the join. A related pair is always two tables, however, you can
also specify a relationship by relating a table to another join relationship.
Depending on the relationship, you specify it either once or twice:
• Key joins relate the primary key of one table to a foreign key in another
table. For key joins you must specify a
PRIMARY KEY and FOREIGN KEY
when you create or alter the underlying tables, using the CREATE TABLE
or ALTER TABLE command.
• For all joins, you specify the relationship when you create the join index,
using the
CREATE JOIN INDEX command. The join is defined by the order
in which you list the tables, by the columns you specify, and by the join
type: key join, natural join, or ON clause join.
Rules for join relationships are:
• Each pair of tables in a join relationship must have at least one join
column.
• The join column must exist in both tables.
• A pair of tables can have more than one join column, as long as they have
the same number of columns and the join column holds the same position
in each table list when you specify it. The order of the lists for the two
tables determines how the columns are matched.