SQL/MX 3.1 Query Guide (H06.23+, J06.12+)

Reviewing Query Execution Plans
HP NonStop SQL/MX Release 3.1 Query Guide—663851-001
4-10
Optimization Tips
For OLTP queries that use OLT optimization, the settings are not relevant because
SQL determines the buffer size depending on the row size.
For non-OLT optimized queries that return or insert multiple rows, the buffer size is
set to the default value to maximize the number of rows returned. If the actual
number of rows is smaller than the default value, only the actual bytes are shipped.
The EXPLAIN function output for the PARTITION_ACCESS operator shows the
size of the buffer that SQL choses and any overrides you caused by setting the
CQDs. Although the maximum size is 31 kilobytes for remote nodes and 56
kilobytes for local nodes, SQL only uses as many bytes as necessary to send the
number of rows in that buffer. If you set a large buffer size, memory consumption is
affected; however, messaging is not affected. If you know a query will return only a
few rows, you can lower the buffer size. If you reduce the buffer size to a small
value and the number of rows returned does not fit, more messages and buffers
will be shipped between DP2 and the executor.
You might want to analyze whether the buffer size is appropriate and whether any
changes you make to the buffer size cause more messaging to occur. You can use
the Measure product and the DISPLAY STATISTICS command to make these
decisions. For information about the Measure product, see the Measure User’s
Guide. For information about DISPLAY STATISTICS, see the SQL/MX Reference
Manual.

JOIN_ORDER_BY_USER
This attribute provides an easy alternative to CONTROL QUERY SHAPE that you
can use when you want to specify the order of the tables but not the join type. The
first table specified in the FROM clause is the outer table in the query.

OPTIMIZATION_LEVEL
The settings for this attribute indicate increasing effort in optimizing SQL queries:
Optimization
Level Description
0 The compiler optimization effort at this level is minimal (it uses
heuristics to perform one-pass optimization for the shortest possible
compilation time). This level is most suitable for small tables or when
plan quality is not important.
TP663851.fm Page 10 Monday, October 17, 2011 11:48 AM