Sybase 12.4.2 User Manual

CHAPTER 4    Adaptive Server IQ Indexes

Any table is only available for write use to a single user at any given time. For 
join indexes, this means that when one user is updating any table in a join 
index, no one else can update any of the tables in that index. All the joined 
tables remain unavailable until the first user’s transaction is committed and you 
have synchronized the tables with the 

SYNCHRONIZE

 command.

Other users receive the following error while the join index tables are in use:

Cannot write to this table in current transaction. 

Another user has write mode access.

 Their current transactions cannot write to any of the join index tables; they 
must begin a new transaction to write to those tables.

For more information on versioning, see Chapter 8, “Transactions and 
Versioning”

Adaptive Server IQ provides a stored procedure, 

sp_iqestjoin

, to help you 

estimate the size of a join index.

You run this procedure for each pair of tables being joined. Each time you run 
the procedure, you must supply the following parameters:

•

Name of the first table to be joined

•

Number of rows in the first table

•

Name of the second table to be joined

•

Number of rows in the second table

•

Relationship (default is one-to-many)

•

IQ page size (default is 65536 bytes, or 64KB)

Many factors affect the size of a join index, especially the number of outer joins 
it includes. For this reason, the procedure offers you three types of results. If 
you know you will always join the tables with exact one-to-one matches, use 
the “Min Case index_size.” If you anticipate occasional one-to-many joins, use 
the “Avg Case index_size.” If you anticipate using numerous one-to-many 
joins, use the “Max Case index_size.”