Single-table join indexes that are not defined as sparse join indexes inherit all statistics from their base table, including dynamic AMP samples and collected statistics.
Only sparse join indexes and multiple-table join indexes require statistics collection. Especially important are statistics on the sparse-defining column in the WHERE clause of a sparse join index or the Optimizer may not select the sparse join index for use.
Consider collecting statistics to improve performance during the following operations.
- Creation of a join index
- Update maintenance of a join index
You need to submit separate COLLECT STATISTICS requests for the columns in the join index and the source columns in the base tables. This does not have a high cost because Vantage can collect statistics while queries are accessing the underlying base tables of a join index.