SQL/MX Comparison Guide for SQL/MP Users

Optimizer and Executor
HP NonStop SQL/MX Comparison Guide for SQL/MP Users—523735-003
5-8
EXPLAIN Features
This table lists some of the differences in default values between NonStop SQL/MP
and NonStop SQL/MX. If you want to mimic an SQL/MP system using NonStop
SQL/MX, you need to reset some default values to get the NonStop SQL/MP behavior.
EXPLAIN Features
Both NonStop SQL/MP and NonStop SQL/MX utilize EXPLAIN features to present
information about query execution plans. In SQL/MP, EXPLAIN is a directive or SQL
utility that describes the execution plan for queries.
In NonStop SQL/MX, you review query execution plans and query performance by
using the EXPLAIN function, DISPLAY_EXPLAIN command, and Visual Query Planner
application. The EXPLAIN function and DISPLAY_EXPLAIN command present query
execution plans in machine-readable format. The Visual Query Planner presents query
execution plans in a graphical user interface through MXCS. Detailed information
about each method is presented in the SQL/MX Query Guide.
Forcing Query Execution Plans
In NonStop SQL/MP, you force query execution plans with options on the CONTROL
TABLE directive on a per table basis. In NonStop SQL/MX, you force query execution
plans by using the CONTROL QUERY SHAPE statement to force the entire plan, not
just a particular table.
The EXPLAIN function and the CONTROL QUERY SHAPE statement use similar
identifiers for the nodes of an operator tree. You can also generate and force query
execution plans through the Visual Query Planner.
Usually, the SQL/MX optimizer presents the best query execution plan. On rare
occasions, you might want to revise the plan presented by the optimizer. Forcing the
plan means that you take control for your query execution plan away from the
optimizer.
To force a plan, you must first run the EXPLAIN function to generate the result table of
the EXPLAIN function for your DML statement. Next, review the result to see if the plan
you received was the plan you wanted. If you determine that you want to change the
plan, use the CONTROL QUERY SHAPE statement to force the plan.
For additional information about forcing query execution plans, see the SQL/MX Query
Guide.
Default NonStop SQL/MP NonStop SQL/MX
ISOLATION_LEVEL STABLE ACCESS READ_COMMITTED
PARALLEL_EXECUTION OFF ON
READ_ONLY CURSOR ON OFF
RECOMPILATION_WARNINGS ON OFF
MDAM OFF ON