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.