Guidelines for Collecting Statistics on Multitable Join Indexes
The guidelines for collecting statistics on the relevant columns are the same as those
for any regular join query that is frequently executed or whose performance is critical.
The only difference with join indexes is that the join result is persistently stored
and maintained by the AMP software without user intervention.
Note the following guidelines for collecting statistics for join indexes.
To improve the performance of creating a join index and maintaining it during updates,
collect statistics on its base tables immediately prior to creating the join index.
Collect statistics on all the indexes defined on your join indexes to provide the
Optimizer with the information it needs to generate an optimal plan.
Collect statistics on additional join index columns that frequently appear in WHERE
clause search conditions, especially when the column is the sort key for a value-ordered
join index because the Optimizer can then use that information to more accurately
compare the cost of using a NUSI-based access path in conjunction with range or equality
conditions specified on the sort key column.
In general, there is no benefit in collecting statistics on a join index for joining
columns specified in the join index definition itself. Statistics related to these
columns should be collected on the underlying base tables rather than on the join
The only time you gain an advantage by collecting statistics on a join column of the
join index definition is when that column is used as a join column to other base tables
in queries where the join index is expected to be used in the Optimizer query plan.