SQL/MX 3.2 Query Guide (H06.25+, J06.14+)

Compiling and Executing a Query
HP NonStop SQL/MX Release 3.2 Query Guide—663851-002
1-16
Improving Query Performance
FROM STAFF_UC
WHERE STAFF_UC.EMPNUM IN
(SELECT WORKS_UC.EMPNUM
FROM WORKS_UC
WHERE WORKS_UC.PNUM IN
(SELECT PROJ_UC.PNUM
FROM PROJ_UC
WHERE
(
PROJ_UC.PNUM < _UCS2 X'00FF'
AND
TRANSLATE (PROJ_UC.CITY USING
UCS2TOISO88591) = 'Tampa'
)
OR
PROJ_UC.CITY = _UCS2 X'5929 6D25'
)
);
--- SQL command prepared.
>>
>>EXECUTE yy;
*** ERROR[8690] An invalid character value encountered in
TRANSLATE function.
--- 0 row(s) selected.
>>
MultiUnion Support
The MultiUnion feature provides enhanced functionality to handle queries that have a
large number of table unions. This feature compresses the binary union backbone in
such queries into a single n-way “MultiUnion” operator, there by reducing query
execution and compilation time. For information on the MultiUnion operator, see
MultiUnion Operator on page 7-46.
The MultiUnion feature is controlled by the MULTIUNION CQD. For more information
on MULTIUNION CQD, see the SQL/MX Reference Manual.
Using Constraint Based Query Pruning
The constraint based query pruning feature enables you to eliminate unwanted table
scans when a predicate in that table violates the check constraints defined on the
Note. You might come across the queries that depend on the data during run time. There is no
specific method to avoid this behavior. However, you can try the following workarounds:
1. Rewrite the query to reorder predicates, tables, and so on.
2. Use control query defaults.
3. Use control query shape.
Note. The MultiUnion feature is supported only on systems running J06.08 and later J-series
RVUs and H06.19 and later H-series RVUs.
TP663851.fm Page 16 Thursday, August 2, 2012 4:47 PM