Sybase 12.4.2 manual Estimating the size of a join index, 169

CHAPTER 4 Adaptive Server IQ Indexes

Table versioning controls access to join 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”

Estimating the size of a join index

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.”

169