SQL/MX 2.x Query Guide (H06.04+, J06.03+)

Reviewing Query Execution Plans
HP NonStop SQL/MX Query Guide—540437-005
4-12
Optimization Tips
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
attribute value is set to ON), NonStop SQL/MX might not push down the plan
because of the plan cost. The system-defined default setting (OFF) means that
NonStop SQL/MX does not attempt to push down.
You can verify if statements have been pushed down to DAM by reviewing the
EXPLAIN output for the plan. If the plan shows the PARTITION_ACCESS operator,
DAM access is being used, and the operators below the PARTITION_ACCESS
operator have been pushed down to DAM.
For a discussion of compound statements, see the SQL/MX Programming Manual
for C and COBOL.
•
REMOTE_ESP_ALLOCATION