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

Reviewing Query Execution Plans
HP NonStop SQL/MX Query Guide—523728-003
4-11
Optimization Tips
To maintain compatibility, the compiler accepts the previous optimization settings
of MINIMUM, MEDIUM and MAXIMUM. The values are mapped as follows:
•
OPTS_PUSH_DOWN_DAM
For compound statements, the predicates for each statement must identify the
DAM process so that the single DAM process is identified by the compiler.
Remember that SQL/MX requires more than one statement within a compound
statement, so the strategy of placing a single SELECT statement between a
BEGIN and END statement to push down the SELECT to the DAM process does
not work.
The first statement contained in the compound statement:
•
Cannot be an INSERT statement with a VALUES clause.
•
If the statement is a SELECT or an UPDATE statement, it cannot have rowset
host variables in the WHERE clause.
For statements (after the first statement) contained in the compound statement:
•
If the statement is an INSERT statement, it cannot have rowset host variables
corresponding to partition key columns inside the VALUE clause.
•
If the statement is a SELECT or UPDATE statement, it cannot have rowset
host variables corresponding to partition key columns in the WHERE clause.
For all statements contained in compound statements:
•
All partition key columns should be covered by the same set of scalar
(nonrowset) host variables so that the compiler can determine that only one
partition can be accessed during run time.
•
Aggregates are not allowed.
Nested joins between no more than three tables and indexes can be considered
for pushing down to DAM. This option can be used for OLTP type of queries
touching a few blocks of participating tables. This option is not recommended for
complex DSS type queries for two reasons:
°
It could considerably increase compile time because of increasing optimization
search space.
°
Concurrent scans on the same physical volume could cause lots of extra
unexpected seeks and query performance could degrade.
When pushing a plan down to DAM is possible (because you have correctly
identified the DAM process in the predicates, and the OPTS_PUSH_DOWN_DAM
NonStop SQL/MX Release 1.x NonStop SQL/MX Release 2.x
MINIMUM 0
MEDIUM 3
MAXIMUM 5