SQL/MP Query Guide

Improving Query Performance Through Query
Design
HP NonStop SQL/MP Query Guide—524488-003
3-20
Controlling the Expansion of Predicates
If the application does not permit a uniform ordering of key columns, an alternative is to
truncate the multivalue predicate so that it excludes the nonconforming column.
Controlling the Expansion of Predicates
If the optimizer determines additional equality predicates are useful, then it considers
each equivalence class separately for expansion. You can affect the expansion by
using a DEFINE for =_SQL_CMP_EQ_LIMIT. This system DEFINE allows you to
specify the number of equivalent predicates that SQL can use to optimize the join order
of the tables and the index selection.
If a join involves numerous tables, the time the optimizer uses to consider the various
combinations of tables can be long. Set the value for the DEFINE low enough to obtain
a reasonable SQL compilation time but high enough to obtain the benefits of the
optimization process.
Table 3-1 shows typical values and their effects on optimization.
The default value for =_SQL_CMP_EQ_LIMIT is 5.
For more information on this DEFINE, see the SQL/MP Reference Manual.
Table 3-2 on page 3-21 describes the rules for expansion of predicates for various
DEFINE values. The optimizer considers the conditions in the table in sequence and
applies the first condition satisfied.
Table 3-1. The Effect of =_SQL_CMP_EQ_LIMIT Values on Compilation Time
=_SQL_CMP_EQ_LIMIT Value Effect on Optimization
0 or 1 SQL does not generate any extra join
predicates.
2 or 3 Increased compile time is negligible.
4 to 6 Compile time is increased but a wider
range of table combinations can occur,
allowing a more efficient possibly query
plan.