17.10 - Guidelines for Collecting Statistics On Hash Index Columns - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1184-171K
Language
English (United States)

The guidelines for selecting hash index columns on which to collect statistics are similar to those for base tables and join 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 do not provide better access plans, report the incident to Teradata support.

When you are considering collecting statistics for a hash index, you can consider the index as a special kind of base table that stores a derived result. For example, any access plan that uses a hash index must access it with a direct probe, a full table scan, or a range scan.

When deciding which columns to include in statistics collection, consider the following:
  • Consult the following table for execution plan issues related to collecting statistics on a hash index:
    IF an execution plan might involve … THEN you should collect statistics on the …
    search condition keys column set that constitutes the search condition.
    joining the hash 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 index definition issues related to collecting statistics on a hash index:
    IF the hash index is defined … THEN you should collect statistics on the …
    with a BY clause or ORDER BY clause (or both) primary index and ordering columns specified by those clauses.
    without a BY clause primary index column set of the base table on which the index is defined.
    without an ORDER BY clause and the ordering column set from the base table is not included in the column_name_1 list order columns of the base table on which the index is defined.

    This action provides the Optimizer with several essential baseline statistics, including the cardinality of the hash index.

  • If a hash 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 hash index.
  • If a hash join index is defined on complex expressions (complex expression being defined as any predicate expression that specifies something other than a simple column reference on the right hand side of a predicate) that are frequently specified as query predicates written using base table column references, collecting statistics on those columns enhances the ability of the Optimizer to estimate single-table cardinalities for a query that specifies the base table columns because it can use those statistics directly to estimate the selectivity of complex expressions on base table columns specified in the query. See Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142.