15.00 - Guidelines for Collecting Statistics On Single-Table Join Index Columns - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1184-015K
Language
English (United States)

Guidelines for Collecting Statistics On Single-Table Join Index Columns

The guidelines for selecting single-table join index columns on which to collect statistics are similar to those for base tables and hash indexes. The primary factor to consider in all cases is whether the statistics provide better access plans. If they do not, consider dropping them. If the statistics you collect produce worse access plans, then you should always report the incident to Teradata support personnel.

When you are considering collecting statistics for a single-table join index, it might help to think of the index as a special kind of base table that stores a derived result. For example, any access plan that uses a single-table join index must access it with a direct probe, a full table scan, or a range scan.

With this in mind, consider the following factors when deciding which columns to collect statistics for:

  • Always consider collecting statistics on the primary index if the join index has one. This is particularly critical for accurate cardinality estimates.
  • Consult the following table for execution plan cases that suggest collecting specific statistics.

IF an execution plan might involve …

THEN collect statistics on the …

search condition keys

column set that constitutes the search condition.

joining the single-table index with another table

join columns to provide the Optimizer with the information it needs to best estimate the cardinalities of the join.

  • Consult the following table for single‑table join index cases that suggest collecting specific statistics.

IF a single-table join index is defined …

THEN you should collect statistics on the …

with an ORDER BY clause

order key specified by that clause.

without an ORDER BY clause and the order key column set from the base table is not included in the column_name_1 list

order key of the base table on which the index is defined.

This action provides the Optimizer with several essential baseline statistics.

  • If a single-table join index column appears frequently in WHERE clauses, you should consider collecting statistics on it as well, particularly if that column is the sort key for a value-ordered single-table join index.
  • If a single‑table join index is defined with a complex expression in its select list that is also specified in a predicate expression written on a mapped base table column, collecting join index statistics on the expression enhances the ability of the Optimizer to estimate single‑table cardinalities for a query that specifies the expression on the base table because it can use those statistics directly to estimate the selectivity of complex expressions on base table columns specified in the query (see SQL Request and Transaction Processing for details).