These are the significant enhancements to the Teradata Database Optimizer in this release.
Improved Performance for TOP n Queries over UNION ALL Views or Derived Tables
Prior to this release, for queries with TOP n over UNION ALL views (or derived tables), the Optimizer would materialize all branches first into a common spool and then apply the TOP N operation on the common spool. This enhancement allows the Optimizer to push unordered TOP n into UNION ALL and, in that way, avoid the cost of spooling some or all branches.
If you generate an EXPLAIN for the query, the output indicates the steps that conditionally process each branch until Teradata Database retrieves the n rows.
- TOP n% queries
- TOP n queries that include ORDER BY clauses
- TOP n queries if an aggregation or statistical function is applied to the UNION ALL view or derived table
Join Index and Aggregate Join Index Optimization Improvements to Help Make Sure Join Indexes Are Used in More Query Scenarios
- If the argument of a SUM expression has CAST applied that is different from an AJI of that SUM (or if the AJI lacks a CAST entirely on the argument), optimization can still use the AJI during query processing, provided there is no loss of precision when converting from the data type of the AJI to the data type specified in the query CAST.
- For an applicable AJI that includes SUM(a) and SUM(b), the Optimizer now
rewrites queries including an expression of the form
SUM(a + b)
SUM(AJI.a) + SUM(AJI.b)
- Join indexes can now partially cover the outer table of outer join. Previously join indexes could not partially cover a query if the join terms in ON-Clause were not fully covered in the join index.
- For an AJI that includes SUM(a), the Optimizer now rewrites queries having
SUM(a * constant)
SUM(AJI.a) * constant
The optimizer also rewrites queries having the form
where b is from a table that's joined on a UPI or USI to the group key of the AJI.
- Join indexes can now be used by the Optimizer to partially cover tables correlated to an EXISTS subquery.
Larger Request Cache
The size of the request cache has been increased for systems that have a control node with at least 250 GB of memory, allowing for more request cache entries. By increasing the number of request cache entries, cached plans may stay in memory longer, improving processing time for requests that have been coded to take advantage of cached plans.