SYNCHRONIZE JOIN INDEX Statement

Synchronizes one or more join indexes after one of their base tables has been updated.

Syntax

SYNCHRONIZE JOIN INDEX [ join-index-name [, join-index-name ]… ]

Examples

Example 1 – Synchronize the join indexes emp_dept_join1 and emp_dept_join2:
```
SYNCHRONIZE JOIN INDEX emp_dept_join1, emp_dept_join2
```

Usage

When a base table that contributes to a join index is updated, Sybase IQ flags the join index as unavailable. Queries that previously took advantage of the join index perform an ad-hoc join instead, perhaps affecting their performance. The SYNCHRONIZE JOIN INDEX command lets you bring the join index up-to-date, making it available for queries to use.

Note: A join index defines a one-to-many relationship (also known as primary key to foreign key) between two table columns. If an insert into the “one” (or primary key) column results in one or more duplicate values, the join index becomes invalid and cannot be synchronized. You must delete the rows containing the duplicate values before SYNCHRONIZE JOIN INDEX can make it valid again.

Synchronizing join indexes can be time-consuming, depending on the size of the base tables that make up the join. It is up to you to decide when to use this command. You can schedule it as a batch job at night or on weekends when you expect your system to have less work to do. You can perform it immediately after Sybase IQ commits a series of inserts and deletes to make the join index available as soon as possible. However, do not synchronize a join index after each insert or delete as the time to update the join index depends on the order of the updates to the tables.

SYNCHRONIZE JOIN INDEX lets you specify multiple join-index-names, separated by commas. You must be the owner of each join index or the DBA. If you do not specify a join-index-name, Sybase IQ synchronizes all the join indexes you own (or all the join indexes in the database if you are the DBA), which might adversely affect the performance of your system.

Standards

SQL—Vendor extension to ISO/ANSI SQL grammar.
Sybase—Not applicable.

Permissions

Must be owner of the join indexes or be DBA.