16.20 - Optimizer Enhancements in Release 16.20 - Teradata Vantage NewSQL Engine

Teradata Vantageā„¢ NewSQL Engine Release Summary

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
created_date
March 2019
category
Release Notes
featnum
B035-1098-162K

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.

This enhancement does not affect:
  • 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

The following enhancements to query rewrite and optimization processing can improve query performance by using join indexes (JIs) and aggregate join indexes (AJIs) for more types of queries:
  • 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)

    to

    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 the form

    SUM(a * constant)

    to

    SUM(AJI.a) * constant

    The optimizer also rewrites queries having the form

    SUM(a*b)

    to

    SUM(AJI.a)*b

    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.