15.00 - Collecting Statistics on Join Indexes - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Teradata Database
Programming Reference

Collecting Statistics on Join Indexes

Single- and multitable join indexes are fundamentally different database objects in many ways, and they are used for different purposes.

As with hash indexes, column statistics for single-table join indexes are generally best collected directly on the base table columns rather than separately as is always required for multitable join indexes.

Note: You cannot collect statistics on a UDT column. This includes UDT columns that are components of an index. The Optimizer uses dynamic AMP sampling information for equality predicates on UDT columns and default selectivity for other predicates on UDT indexes for costing. The dynamic AMP sampling provides limited statistics information about the index. For example, it cannot detect nulls or skew. If a UDT index access path does not show any improved performance, you should consider dropping the index to avoid the overhead involved in its storage and maintenance.

An important exception to this guideline is the case where a single‑table join index is defined on a complex expression that is also frequently specified as a term in predicate expressions for queries made on the relevant base table column (in this context, a complex expression is defined as an expression that specifies something other than a simple column reference on the left hand side of a predicate.

Always consider using theSAMPLE options when you collect and recollect statistics on a join index. See “Reducing the Cost of Collecting Statistics by Sampling” on page 179 for further information about these options and recommendations on how to use them.

The Optimizer can only use statistics collected on complex expressions that can be mapped completely to a single‑table join index or hash index expression. Collecting statistics on the join index column for those expressions enhances the ability of the Optimizer to estimate single‑table cardinalities for a query that specifies the base table expressions in its predicate. See SQL Request and Transaction Processing.

The following table points to the various topics that explain the different recommendations for collecting statistics for single-table and multitable join indexes:


FOR this type of join index …

The following topics explain how best to collect statistics on the index …


  • “Collecting Statistics on a Single-Table Join Index” on page 376.
  • “Collecting Statistics on Base Table Columns Instead of Single-Table Join Index Columns” on page 377.
  • “Guidelines for Collecting Statistics On Single-Table Join Index Columns” on page 378.
  • multitable

    “Collecting Statistics on Multitable Join Indexes” on page 379.


  • “Collecting Statistics on a Single-Table Join Index” on page 376.
  • “Collecting Statistics on Base Table Columns Instead of Single-Table Join Index Columns” on page 377.
  • For general information about collecting statistics, see “COLLECT STATISTICS (Optimizer Form)” on page 175).