Sybase 12.4.2 Defining join relationships between tables, Performance hints for synchronization

CHAPTER 4 Adaptive Server IQ Indexes

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.

159