Purpose
Reports a summary of the query plan generated by the SQL query optimizer to process any valid SQL request, that is, the steps the system would use to resolve a request. The request itself is not executed, except in the case of a dynamic plan where the request is partially executed.
Optionally returns the output as XML text instead of plain English text.
For additional information about how to interpret the reports generated by an EXPLAIN request modifier or its related Visual Explain utility, see:
- “INSERT EXPLAIN”
- SQL Request and Transaction Processing
- Teradata Visual Explain User Guide
Required Privileges
To EXPLAIN a request, you must have the permissions that are required to execute that request.
Syntax
Syntax Elements
- STATIC
- An EXPLAIN for the static plan is generated. If applicable, the static plan indicates the request is eligible for incremental planning and execution (IPE). By default, a static plan is generated. The Optimizer processes an explained request the same as the request would be processed without being modified by the EXPLAIN, without actually executing the SQL within the request.
- DYNAMIC
- An EXPLAIN for the dynamic plan or the static plan is generated based on the eligibility and the qualification of Incremental Planning and Execution (IPE). A request is eligible for IPE when the optimizer can use runtime feedback in its planning. A request is qualified for IPE if the request is eligible and cost thresholds are met. The dynamic plan is generated incrementally while the optimizer uses runtime feedback in its planning. If the Optimizer determines that the request is:
- Qualified for IPE and displaying a dynamic plan is allowed, a dynamic plan is returned. All of the plan fragments except the last one are executed to generate this dynamic plan. Values selected for intermediate results in a previous plan fragment can be masked with :* (colon asterisk) characters in subsequent plan fragments in the display, based on SecureExplain DBS Control field. In addition to the plan, the real execution information such as the actual number of rows in a spool and the actual size of a spool are described for executed fragments. For more information, see Dynamic Feedback.
- Eligible for IPE, but the SecureExplain DBS Control field are not set to allow returning a dynamic plan, a static plan is returned with an indication that the request is eligible for IPE.
- Not eligible for IPE, a static plan is returned.
- IN XML
- Return the output as XML text rather than as plain English text. For more information, see SQL Data Definition Language.
- NO DDLTEXT
- Do not capture the DDL text in the XML EXPLAIN text.
- SQL_request
- Text of an SQL request about which Optimizer processing information is desired.
ANSI Compliance
EXPLAIN is a Teradata extension to the ANSI SQL:2011 standard.
Other SQL dialects support similar non-ANSI standard statements with names such as EXPLAIN PLAN.