The EXPLAIN request modifier reports an English language summary of the request plan generated by the Optimizer to process any valid SQL request. The summary describes the AMP steps the system uses to resolve the request, but the request is not processed unless the Optimizer uses a dynamic plan and then the request is partially processed.
To EXPLAIN a request, you must have the privileges required to run the request.
EXPLAIN provides a summary of the access and join plans generated by the Optimizer for the specified SQL request, including information such as:
- Indexes to use
- Intermediate spool files to be generated (if any)
- Join types to perform
- Whether to dispatch the requests in a transaction in parallel
- Relative time to complete the request
- Whether to use a dynamic or static request plan
Using EXPLAIN allows you to compare the relative performance of the different versions of a request and choose the most efficient SQL.
Recommendation: Use EXPLAIN to analyze any new requests under development. Subtle differences in the way a request is structured can produce significant differences in its resource impact and performance, even though the requests may produce identical end results.