SQL/MX 3.x Query Guide (H06.22+, J06.11+)

Compiling and Executing a Query
HP NonStop SQL/MX Query Guide—640323-001
1-17
Improving Query Performance
table. This feature enhances query performance by reducing compilation and
execution time.
During query compilation, NonStop SQL/MX receives the inputs from the constraints
that are defined on a table. If the query contains the predicates that fall outside the
check constraints on the table, the scan nodes are marked as “not returning any
records”. In other words, if the predicates are such that there is no need to scan the
table, the scan nodes will be replaced by the VALUES nodes. As a result, the table is
not scanned during query execution, thereby improving the query execution.
Example 1-2 shows the output of the EXPLAIN statement for a query when the Control
Query Default (CQD), CHECK_CONSTRAINT_PRUNING, is set to ON and OFF.
For information on CHECK_CONSTRAINT_PRUNING CQD, see the SQL/MX
Reference Manual.
Note. The constraint based query pruning feature is supported only on systems running
J06.08 and later J-series RVUs and H06.19 and later H-series RVUs.
Note. The constraint-based query pruning feature has the following limitations:
1. Only scan node is converted to the VALUES node.
2. The pruning logic is not rolled-up to other nodes.
3. The constraint based query pruning feature includes the following operators that are used
in table constraints or selection predicates or both:
<, >, <=, >=, =, <>, IS NULL, IS NOT NULL
4. The pruning logic does not roll-up for correlated sub queries.
5. The pruning logic does not work for OR predicates. It works only on AND predicates and
constraints.