16.10 - Statistical Inheritance by Hash and Join Indexes - Teradata Database

Teradata Database SQL Request and Transaction Processing

prodname
Teradata Database
vrm_release
16.10
created_date
June 2017
category
Programming Reference
User Guide
featnum
B035-1142-161K

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.