Inserting into Tables of a Join Index

You load or insert data into the tables underlying a join index, just as you would any other indexes. There is only one difference: the data in a join index must be synchronized before you can use the join index to resolve queries.

Note: You cannot update a base table that is part of any join index. You can only insert, load, or delete.

When you first create a join index, Sybase IQ synchronizes the join index for you automatically. It does not matter whether you create the join index before or after loading. The order also does not affect performance of the load or synchronization.

Once you have created a join index, however, if you insert or load data into any of its underlying tables except the top table in the join hierarchy, you must synchronize it explicitly. To do so, use the SYNCHRONIZE JOIN INDEX statement. See Reference: Statements and Options.

Once any user has updated any of the tables in a join index, no other user can update any of the tables underlying that join index until the join index has been synchronized.

Updating from Different Connections May Cause Errors

When more than one user inserts into or deletes from different tables that participate in the same join index, the second user's update will fail unless the synchronize commits before the second user’s transaction starts. This failure occurs if either of the following conditions exist:

The second user's transaction begins before the first user's transaction commits.
The second user tries to update after the first user's transaction commits, but before the join index is synchronized.

This problem occurs because Sybase IQ makes a new version of the join index when any of its underlying tables is updated. The new version is not visible to other transactions that have already begun. The problem does not occur when one user makes all of the changes, because the newer table version is visible to the user who made the original changes.

For example, assume that tables A, B, and C are all members of the same join index. User 2 begins a transaction, and writes to another table not involved in the join. Now, User 1 inserts into table B. This action creates a new version of table B, and a new version of the join index. User 2 then tries to write to table C. Even though no other user has changed table C, because C is a member of the join index it can't be updated until the join index is synchronized.