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

Merge Join is an efficient join implementation strategy if the physical schema was designed
to take advantage of it. For example, large tables are physically ordered based on the most
frequently joined column(s).
Production usage
Turning Merge Join OFF may result in the optimizer not considering potentially efficient
query plans, for queries with large joins on tables that are physically ordered by the join
column(s). Turning Merge Join ON causes an increase in compile time because the optimizer
now has to consider many more join options.
Impact
Set this CQD at the query level when a Merge Join is not working efficiently for a specific
query. However, there may be cases (usually a defect) where a larger set of queries is being
negatively impacted by Merge Joins. In those cases you may want to set it at the service or
system level.
Level
Avoid turning all the 3 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
NESTED_JOINS
Influencing Query PlansCategory
Determines if Nested Join is considered by the optimizer to generate an execution plan.Description
Nested Join is considered.‘ON’
Nested Join is disabled.‘OFF’
The default value is ‘ON’.
Values
Use this CQD when you want to force a query plan not to use Nested Joins. This is useful
as a workaround for query plans with very expensive Nested Joins, which may occur if
the optimizer fails to estimate the cost of a Nested Join correctly.
Usage
Nested Join is an important join implementation strategy for many BI queries. It is
recommended not to turn Nested Join OFF. It should only be used to force a query plan
for a particular query on an exception basis.
Production usage
Turning Nested Join OFF may result in inefficient query plans for certain type of queries,
such as light workloads and star join queries.
Impact
QueryLevel
Avoid turning all the 3 join implementations OFF (Hash Joins, Nested Joins, and Merge
Joins). This may result in the compiler failing to generate query plans.
Conflicts/Synergies
The problem of inefficient Nested Joins can be better handled using a higher degree of
query plan robustness as set by the ROBUST_QUERY_OPTIMIZATION CQD.
Addressing the real
problem
Neoview Release 1.0Introduced in release
Not applicableDeprecated in release
OPTIMIZATION_LEVEL
Influencing Query PlansCategory
Controls the optimizer resources and time spent for optimizing a query plan, with level 0
indicating the least amount of optimization effort and level 5 indicating the most. Lower
optimization levels produce lower plan quality with minimal compile time, while higher
optimization levels cause the compiler to spend more compilation time to produce better
plan quality.
Description
NESTED_JOINS 23