15.00 - Collecting Statistics for Join Indexes - Teradata Database

Teradata Database Design

Teradata Database
User Guide

Collecting Statistics for Join Indexes

Hash indexes and 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 multitable join indexes require statistics collection. It is particularly important that statistics be collected on the sparse-defining column in the WHERE clause of a sparse join index or the Optimizer might 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 very high cost because Teradata Database can collect statistics while queries are accessing the underlying base tables of a join index.