15.00 - Determining When the Optimizer Will Consider Group AMP Processing - Teradata Database

Teradata Database Design

Teradata Database
User Guide

Determining When the Optimizer Will Consider Group AMP Processing

The Optimizer considers several criteria when it determines whether a query is a candidate for Group AMP processing or not:

  • How many AMPs needed to satisfy the request?
  • For most systems this number of participating AMPs must be 50% or fewer of the total number of AMPs configured in the system. Statistics collected on the selection and join columns being referenced in the query help the Optimizer make the correct assessment. If no statistics for these columns exist, the Group AMP option is less likely to be chosen. This makes the regular collection of statistics a critical prerequisite for gaining the advantages of the Group AMP feature.

  • Can the query be driven from a single-AMP or Group AMP step as the first meaningful database access step?
  • Because NUSI access is always an all-AMP activity, accessing rows by means of a NUSI as the first step in the query plan eliminates Group AMP considerations later in the plan, even if the NUSI access returns only one row from one AMP.

  • Does a product join based on table duplication appear in any step in the query plan?
  • If so, the Group AMP option is not considered. Under this condition, a product join is always an all-AMP operation. The existence of a single all‑AMP operation in the plan negates the possibility of using a Group AMP later.