Teradata Database now employs the following optimization strategies for queries that involve the UNION ALL set operator:
- Pushing of aggregations and joins from the outer block into the UNION ALL branches, based on cost and other factors.
- Evaluation of pushed aggregations and joins as multisource operations, when applicable.
- Smarter construction and more accurate derivation of demographics for UNION ALL spools.
- Elimination of unsatisfiable UNION ALL branches using partitioning expressions as constraints.
This feature improves the performance of queries that involve the UNION ALL set operator by reducing the size of the UNION ALL spool when aggregations and joins are pushed into the UNION ALL branches. Pushing aggregations and joins into the UNION ALL branches also provides the Optimizer with more plan choices and better overall performance.
These optimizations are useful, for example, where historical data is kept in one table and current data is kept in another that is actively accessed. If, for some queries, historical data or both current and historical data need to be accessed, a user could access the two tables as if they were one table using a view that is the UNION ALL of the two tables. The UNION ALL optimizations can limit access to one or the other table based on partitioning constraints, CHECK constraints, and query conditions. Alternatively, if there must be access to both tables, the optimizations can improve the performance when joins with other tables or aggregation are used. The user need not be concerned about which table to access, or if both tables need to be accessed.
You should be aware of the following considerations when using UNION ALL:
- UNION ALL views and derived tables must not have any other set operators, such as MINUS, INTERSECT, or UNION DISTINCT.
- UNION ALL SELECT branches with aggregations, DISTINCT, statistical functions, sampling, expand, and normalize are not eligible for these optimizations.
- Aggregations and joins are not always pushed into UNION ALL branches, in particular, when other plans have lower estimated cost, or when plans to push aggregations and joins are not considered by the Optimizer due to their complexity, or to avoid increased parsing time.
- There could be a slight increase in parsing time, as a result of the optimizer having more options to consider.
- As for all cost-based optimizations, you must collect applicable statistics to get the most benefit from this feature.
For more information on UNION ALL optimizations, see Teradata® Database SQL Request and Transaction Processing, B035-1142.