The guidelines for selecting single-table join index columns on which to collect statistics are similar to those for base tables. The primary factor to consider is whether the statistics provide better access plans. If not, consider dropping them. If the statistics you collect produce worse access plans, then report the incident to Teradata Support.
When considering collecting statistics for a single-table join index, think of the index as a base table that stores a derived result. An access plan that uses a single-table join index must access the index with a direct probe, a full table scan, or a range scan. Therefore, consider the following factors when choosing columns for which to collect statistics.
- Consider collecting statistics on the primary index. This is critical for accurate cardinality estimates.
Situation Where to Collect Statistics Run plan may involve search condition keys. On column set that constitutes search condition predicate. Run plan may involve joining the single-table index with another table. On join columns, to provide Optimizer with information needed to best estimate cardinalities of join. Single-Table Join Index Where to Collect Statistics Defined with an ORDER BY clause On order key specified by that clause. Defined without an ORDER BY clause and the order key column set from the base table is not included in the column_name_1 list On order key of the base table on which the index is defined. This action provides the Optimizer with essential baseline statistics.
- If a single-table join index column appears frequently in WHERE clause predicates, consider collecting statistics on the join index too, particularly if that column is the sort key for a value-ordered single-table join index.