Neoview Control Query Default (CQD) Reference Guide (R2.5)

5 Optimizer
This section describes these CQDs that are used by the Optimizer:
• “HASH_JOINS” (page 21)
• “JOIN_ORDER_BY_USER” (page 21)
• “MDAM_SCAN_METHOD” (page 22)
• “MERGE_JOINS” (page 22)
• “NESTED_JOINS” (page 23)
• “OPTIMIZATION_LEVEL” (page 23)
• “RISK_PREMIUM_NJ” (page 24)
• “RISK_PREMIUM_SERIAL” (page 25)
• “ROBUST_QUERY_OPTIMIZATION” (page 26)
• “SUBQUERY_UNNESTING” (page 28)
HASH_JOINS
Update Statistics and ReorgCategory
Determines if Hash Join is considered by the optimizer to generate an execution plan.Description
Hash Join is considered.‘ON’
Hash Join is disabled.‘OFF’
The default value is ‘ON’.
Values
Use this CQD when you want to force a query plan not to use any Hash Joins.Usage
Hash Join is an important join implementation strategy for most BI queries. It is highly
recommended not to turn Hash Join OFF. It should only be used to force a query plan for
a particular query on an exception basis.
Production usage
Turning Hash Join OFF may result in very inefficient query plans with expensive nested
joins or sorts for merge joins.
Impact
QueryLevel
Avoid turning all the three join implementations OFF (Hash Joins, Nested Joins, and Merge
Joins). This may result in the compiler failing to generate query plans.
Conflicts/Synergies
Not applicableAddressing the real
problem
Neoview Release 1.0Introduced in release
Not applicableDeprecated in release
JOIN_ORDER_BY_USER
Influencing Query PlansCategory
Enables or disables the join order in which the optimizer joins the tables to be the sequence
of the tables in the FROM clause of the query.
Description
Join order is forced.‘ON’
Join order is decided by the optimizer.‘OFF’
The default value is ‘OFF’.
Values
When set to ON, the optimizer considers only execution plans that have the join order
matching the sequence of the tables in the FROM clause.
Usage
HASH_JOINS 21