15.00 - Summary of Join Index Functions - Teradata Database

Teradata Database Design

Teradata Database
User Guide

Summary of Join Index Functions

A join index always has at least one of the following functions.

  • Replicates all, or a vertical subset, of a single base table and partitions its rows using a different primary index (or a different column partitioning if the base table is a column‑partitioned table) than the base table, such as a foreign key column to facilitate joins of very large tables by hashing them to the same AMP.
  • Note: A partitioning expression for a PPI join index or column‑partitioned join index cannot contain a row‑level security constraint column.

  • Joins multiple tables (optionally with aggregation) in a prejoin table.
  • Aggregates one or more columns of a single table as a summary table.
  • Join indexes are updated automatically, so the only administrative task a DBA must perform is to keep the statistics on multitable join index columns and their indexes current. For non‑sparse single-table join indexes, the best policy is to use base table statistics rather than to collect statistics directly on the columns of the index.

    The recommended practice for recollecting statistics is to set appropriate thresholds for recollection using the THRESHOLD options of the COLLECT STATISTICS statement. See “COLLECT STATISTICS in SQL Data Definition Language for details on how to do this.

    You cannot collect statistics on complex expressions specified in a base table definition. However, if you frequently submit queries that specify complex base table predicate expressions, you can create a single‑table join index or hash index that specifies those frequently used predicate expressions in its select list or column list, respectively, and then collect statistics on the expression defined as a simple column in your index.

    There are several specific cases where join index statistics can provide more accurate cardinality estimates than are otherwise available for base table predicates written using complex date expressions.

  • The case where an EXTRACT expression specified in a query predicate can be matched with a join index predicate.
  • The case where an EXTRACT/DATE expression specified in a query predicate condition can be mapped to an expression specified in the select list of a join index.
  • The Optimizer uses expression mapping when it detects an identical query expression or a matching query expression subset within a non-matching predicate. When this occurs, the Optimizer maps the predicate to the identical column of the join index, which enables it to use the statistics collected on the join index column to estimate the cardinality of the expression result.

    When you create a single‑table join index that specifies a complex expression, Teradata Database transforms the expression into a simple join index column. This enables the Optimizer to map the statistics collected on those complex expressions to the base table to facilitate single‑table cardinality estimates or to match the predicates (see SQL Request and Transaction Processing for details).

    Note that the derived statistics framework supports bidirectional inheritance of statistics between a non-sparse single‑table join index and the base table it supports (see SQL Request and Transaction Processing for details), so the entity on which statistics are collected is no longer as important as it once was.