Teradata Database now processes single or multiple instances of COUNT DISTINCT, common in tactical queries, more efficiently. Release 16.00 processes multiple count distinct operations in fewer steps. EXPLAIN text has been modified to differentiate aggregate steps that combine aggregate and DISTINCT operations by indicating the number of distinct and non-distinct aggregates that would be processed by the SUM step.
MINIMUM DISTINCT and MAXIMUM DISTINCT operations are processed as a MINIMUM DISTINCT or a MAXIMUM DISTINCT non-distinct aggregate. This methodology also enables query processing with a reduced number of steps, and when specified with COUNT DISTINCT, these operations are processed by using the multi-distinct aggregate SUM as a non-distinct aggregate.
Benefits
The primary benefit of this feature is improved performance of queries that use COUNT DISTINCT syntax.
Considerations
- Collect statistics on the anticipated GROUP BY values.
- If possible, also collect statistics on the distinct expressions.
Additional Information
For more information on performance improvements for COUNT DISTINCT operations, see Teradata Vantage™ SQL Request and Transaction Processing, B035-1142.