16.20 - Collecting Statistics on Join Indexes - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Detailed Topics

Teradata Database
Teradata Vantage NewSQL Engine
Release Number
Release Date
March 2019
Content Type
Programming Reference
Publication ID
English (United States)

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.

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 the SAMPLE options when you collect and recollect statistics on a join index. See Reducing the Cost of Collecting Statistics by Sampling 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 Teradata Vantage™ SQL Request and Transaction Processing, B035-1142.

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

For general information about collecting statistics, see COLLECT STATISTICS (Optimizer Form).