How to Estimate Join Index Size

Sybase 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 131072 bytes, or 128KB)

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

These calculations should give you an idea of how much disk space you need for the join index. The results include the segment size in bytes, and the number of blocks. The procedure also tells you how long it will take to create the join index.

If you want to know the actual size of an existing join index, you use a different stored procedure, sp_iqjoinindexsize.