SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
X-10
=_SQL_CMP_EQ_LIMIT DEFINE
=_SQL_CMP_EQ_LIMIT DEFINE
=_SQL_CMP_EQ_LIMIT is a system DEFINE that specifies the number of expressions
in an equivalence class for which the query rewrite feature adds equality predicates.
xnn
is a string containing a single arbitrary alphabetic character followed by one or two
digits that represent a decimal value between 0 and 99. If you do not use this
DEFINE, SQL uses a default value of 5.
Consideration—=_SQL_CMP_EQ_LIMIT
The optimizer evaluates join plans for different combinations of tables. If the join
involves numerous tables, this selection process can be inefficient. When choosing a
value for =_SQL_CMP_EQ_LIMIT, set the DEFINE low enough to obtain a reasonable
SQL compilation time but high enough to obtain the benefits of the optimization
process. Typical values are:
Examples—=_SQL_CMP_EQ_LIMIT

This TACL command specifies a limit of four equivalent predicates for join order
and index selection:
32> ADD DEFINE =_SQL_CMP_EQ_LIMIT, CLASS MAP, FILE T4

This TACL command directs SQL to use its default value for the number of
equivalent predicates:
34> DELETE DEFINE =_SQL_CMP_EQ_LIMIT
ADD DEFINE =_SQL_CMP_EQ_LIMIT, CLASS MAP, FILE xnn
0 or 1 SQL does not generate any additional equivalent predicates
2 or 3 Increased compile time is negligible
4 to 6 Some increased compile time but a wider range of table
combinations, allowing a more efficient query plan