Collecting Statistics on Base Table Columns Instead of Single-Table Join Index Columns - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Advanced SQL Engine
Teradata Database
Release Number
July 2021
English (United States)
Last Update
Product Category
Teradata Vantage™

The Optimizer substitutes base table statistics for single-table join index statistics when no demographics have been collected for its single-table indexes. Because of the way single-table join index columns are built, it is generally best not to collect statistics directly on the index columns and instead to collect them on the corresponding columns of the base table. This optimizes both system performance and disk storage by eliminating the need to collect the same data redundantly.

This recommendation does not apply to sparse join indexes. If the join index is sparse, you should collect statistics on the join index itself rather than on its underlying base table.

Also, this recommendation does not apply to the case where a single-table join index is defined using a complex expression in its select list that is frequently specified in predicates used in queries made against the mapped base table columns in the expression. See Collecting Statistics on Join Indexes and Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142.

There are three reasons why you might need to collect statistics on single-table join index columns instead of their underlying base table columns.
  • The single-table join index is sparse (see Sparse Join Indexes).
  • If you decide not to collect the statistics for the relevant base table columns for some reason, then you should collect them directly on the corresponding single-table join index columns.
  • If the primary index column set consists of more than one column, and there is no primary or secondary index on the base table that includes those columns, then you have two options:
    • The better option is to collect multiple column statistics on the base table (see Collecting Statistics on Multiple Columns).
    • A much poorer option is to collect the statistics on the column set for the single-table join index. If you do this, you must use the COLLECT STATISTICS ON … INDEX syntax to collect the statistics for that column set on the single-table join index.