15.00 - Statistics and Other Demographic Data for Join Indexes - Teradata Database

Teradata Database Design

Teradata Database
User Guide

Statistics and Other Demographic Data for Join Indexes

Collect statistics on the indexes of a join index to provide the Optimizer with the information it needs to generate an optimal plan.

The COLLECT STATISTICS (Optimizer Form) statement collects demographics, computes statistics from them, and writes the resulting data into individual entries for each individual base table and join index table on the system.

As far as the Optimizer is concerned, a multitable join index and the base tables it supports are entirely separate entities. You must collect statistics on multitable join index columns separately from the statistics you collect on their underlying base table columns because the column statistics for multitable join indexes and their underlying base tables are not interchangeable.

On the other hand, it is generally preferable to collect statistics on the underlying base table of a single‑table join index and not directly on the join index columns. See “Guidelines for Collecting Statistics on Single-Table Join Indexes” on page 596 and “Collecting Statistics on Base Table Columns Instead of Single-Table Join Index Columns” on page 596 for further details on collecting statistics for single‑table join indexes. Note that the derived statistics framework supports bidirectional inheritance of statistics between a non-sparse single‑table join index and its underlying base table. See SQL Request and Transaction Processing for details.