Statistical Inheritance by Hash and Join Indexes | Optimizer Process | Vantage - 17.10 - Statistical Inheritance by Hash and Join Indexes - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - SQL Request and Transaction Processing

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
User Guide
Publication ID
B035-1142-171K
Language
English (United States)

Single-table non-sparse join and hash indexes inherit their statistics from their underlying base table if the statistics have not been collected on the index itself. This applies to single-column statistics, single-column-index statistics, multicolumn statistics, and multicolumn index statistics.

Available Forms of Statistical Inheritance

The following list summarizes the available forms of statistical inheritance:
  • Underlying base tables can inherit the statistics from any single-table non-spare join and hash indexes that reference them. In this case, it becomes possible to inherit statistics from multiple qualified join and hash indexes.
  • Multicolumn statistics are inherited from an underlying base table by a single-table non-sparse join index or hash index defined on it.
  • Partitioning columns can inherit the statistics from the system-derived PARTITION column, but only if the table is partitioned by a single-column partitioning expression.
  • Bidirectional inheritance of statistics is implemented for single-table non-sparse join indexes and hash indexes so that statistics collected on the single-table non-sparse join or hash index are inherited by their underlying base table.

    For this case, it is possible for there to be multiple qualified single-table non-sparse join or hash indexes from which a base table could inherit statistics.

  • Bidirectional inheritance of statistics is implemented for PARTITION statistics, where those statistics are inherited by the partitioning column of a row-partitioned table if the table is partitioned by a single-column partitioning expression.

    Bidirectional inheritance of statistics is not supported for PARTITION#L n statistics.

The inheritance of statistics occurs when the derived statistics are built for the base relations. All statistical inheritance is bidirectional.