15.00 - Guidelines for Collecting Statistics on Multitable Join Indexes - Teradata Database

Teradata Database Design

Teradata Database
User Guide

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 index.
  • 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.