NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
Z-9
=_SQL_CMP_EQ_LIMIT DEFINE
is present, the optimizer also considers using double buffering for these two types of
join operations.
•
If both =_SQL_CMP_DOUBLE_SBB_ON and
=_SQL_CMP_DOUBLE_SBB_OFF are present, file-system double buffering will
not be used.
•
Double buffering can increase performance, but can also increase the likelihood of
PFS memory overflow. See the NonStop SQL/MP Installation and Management
Guide for a full discussion of how to manage double buffering.
=_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.
Considerations—=_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 as follows:
Examples—=_SQL_CMP_EQ_LIMIT
•
The following 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
•
The following 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