16.20 - In-Memory Optimization Enhancements - 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

In-memory optimization techniques have been enhanced with support for the following new features:

  • In-memory hash joins for two-step CP join, outer hash join, and PRPD join.
  • In-memory hash joins and bulk qualification for DML statements.
  • Single-instruction-multiple-data (SIMD) data-level parallel processing for :
    • Predicate comparison across different data types (for example, BYTEINT and INT)
    • DECIMAL data type comparisons
    • Hash computation in dynamic hash joins
  • New AllRowsOneAMP in-memory hash join, where the in-memory optimized spool is duplicated to just one AMP.
  • Enhanced MVC-aware bulk qualification to more comparison operators. Supports MVC with run length (RL) compression and RL-aware bulk qualification.

Benefits

  • Improves CPU performance by supporting in-memory hash join and bulk qualification in more areas.
  • Improves I/O and CPU performance by duplicating in-memory optimized spool to only one AMP.
  • Improves CPU performance by doing bulk qualification without the need to decompress MVC-compressed and RL-compressed data.
  • Improves CPU performance of predicate and DECIMAL comparisons, and hash computations in dynamic hash joins.

Considerations

  • Performance improvements for predicate and DECIMAL comparisons, and hash computations in dynamic hash joins require newer Teradata systems that use Intel "Haswell" and newer CPU architectures (Teradata 6800/2800/1800/680 and later).
  • Compression-aware bulk qualification is applicable only to auto-compressed column-partitioned data.
  • AllRowsOneAMP in-memory hash join optimizations are not applicable if the join is a dynamic row-partition elimination (DPE) in-memory hash join or for in-memory hash joins that are part of partial redistribution and partial duplication (PRPD) optimizations.
  • Resource consumption mode of AllRowsOneAMP in-memory hash joins can improve resource consumption (I/O and CPU), but this comes at the expense of elapsed time.

Additional Information

For more information on query rewrite and optimization, see Teradata Vantageā„¢ SQL Request and Transaction Processing, B035-1142.