Sybase Adaptive Server IQ 12.4.2, Administration And Performance Manual

Page: 189 / 536

CHAPTER 4 Adaptive Server IQ Indexes
169
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.”