SQL/MX 2.x Query Guide (G06.24+, H06.03+)

Compiling and Executing a Query
HP NonStop SQL/MX Query Guide—523728-003
1-9
Improving Query Performance
You can also use the DISPLAY_EXPLAIN OPTIONS 'f' command to display operators
in the query. Check the OPT column for letter o entries, as shown, to determine
whether OLT optimization is enabled.
LC RC OP OPERATOR OPT DESCRIPTION CARD
-- -- -- --------- ----- ------------ -------
2 . 3 root o r 1.00E+0
1 . 2 partition_access o 1.00E+0
. . 1 insert o T 1.00E+0
To determine if your query uses OLT optimization, check the query type and operators
listed in Table 1-1.
If your query is using OLT optimization and performance is still an issue, review the
database or query for design choices that can affect performance. The SQL/MX
Note. For more information on how to use the DISPLAY_EXPLAIN OPTIONS 'f' command,
see the SQL/MX Reference Manual.
Table 1-1. Valid Queries and Operators for OLT Optimization
Query ROOT Operator
PARTITION_ACCESS
Operator DAM Operators
Embedded unique UPDATE Yes Yes Yes
Embedded unique DELETE Yes Yes Yes
Embedded unique INSERT Yes Yes Yes
Embedded unique SELECT
... into
Yes Yes Yes
Embedded nonunique
UPDATE
Yes Yes No
Embedded nonunique
DELETE
Yes Yes No
Embedded INSERT with
value list
Yes No Yes
Embedded nonunique
SELECT ... into
Yes No No
Dynamic MXCI & ODBC
queries
No Same considerations
as Embedded Queries
Same
considerations
as Embedded
Queries
Compound Statements Yes Only if forced to use
DAM
Yes
Rowsets No No Unique access
only