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:
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.
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.
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.