Purpose
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.