EXPLAIN Request Modifier | Teradata Vantage - EXPLAIN Request Modifier - Analytics Database - Teradata Vantage

Database Administration

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
ft:locale
en-US
ft:lastEdition
2024-10-04
dita:mapPath
pgf1628096104492.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
ujp1472240543947
lifecycle
latest
Product Category
Teradata Vantageā„¢

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 would perform 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 execute the request.
EXPLAIN is useful for evaluating and optimizing SQL requests. It provides a summary of the access and join plans generated by the Optimizer for the specified SQL request, including information such as:
  • The indexes to be used
  • Any intermediate spool files that will be generated
  • The join types to be performed
  • Whether the requests in a transaction will be dispatched in parallel
  • The relative time it will take to complete the request
  • Whether a dynamic or static request plan will be used

Using EXPLAIN allows you to compare the relative performance of the different versions of a request and choose the most efficient SQL.

Recommendation: Always 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.