17.10 - HJ2IMHJ - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Database Utilities

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Configuration
Publication ID
B035-1102-171K
Language
English (United States)

Determines whether the Optimizer preferentially chooses the In-Memory Hash Join method for performing hash joins and dynamic hash joins. Also determines whether the Optimizer evaluates the In-Memory Hash Join method as an alternative path for costing.

Field Group

Performance

Valid Settings

Setting Description
TRUE Hash join and dynamic hash joins are preferentially performed using the In-Memory Hash Join method whenever possible.
FALSE All hash join and dynamic hash join methods, including In-Memory, are evaluated by the Optimizer, which chooses the most cost-effective method to use.

Default

FALSE

Changes Take Effect

After the DBS Control Record has been written.

Usage Notes

When two tables are joined, the Optimizer selects the best method to accomplish the join by predicting and comparing the costs of all possible join methods (for example, merge join, hash join, in-memory hash join, product join, nested join) using various different potential data geographies (local, duplicated, and hash distributed) for the tables.

Setting this field to TRUE causes the Optimizer to use the In-Memory Hash Join method whenever possible, in preference to any other hash join method, regardless of costs. The In-Memory method offers generally superior performance over other hash-join methods. Setting this field to TRUE also avoids generation of cost-based plans for in-memory hash joins, and therefore can save time.