When prepended to an SQL request, the EXPLAIN request modifier returns a summary of the static, step-by-step Optimizer plan for processing the SQL request. For the syntax, usage notes, and other operational aspects of the EXPLAIN request modifier, see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.
EXPLAIN is an extremely useful utility for query designers. The returned output lists the steps the Optimizer means to take to process the request and the estimated time required to complete the request, given the statistics the Optimizer has to work with. The output shows which indexes the Optimizer means to use to process the request, identifies any intermediate spool generated, indicates the types of join to be performed, shows whether the requests in a transaction is dispatched in parallel, and includes other information specific to the request.
EXPLAIN can help you evaluate queries and develop alternative, more efficient, processing strategies. For requests qualifying for incremental planning and execution (IPE), DYNAMIC EXPLAIN provides a similar function.
The times in the EXPLAIN output are presented in seconds, but are arbitrary units of time that allow you to compare the relative performance of alternate coding formulations of the same query.
The plan for a request as ultimately run on the system may differ from the plan presented in the EXPLAIN output if system conditions have changed. For example, due to changes in system statistics and data demographics or due to dynamic planning for IPE, which can change plan steps based on the results of previous steps. You can use DBQL to log the EXPLAIN output associated with the actual execution of a query. See Teradata Vantage™ - Database Administration, B035-1093.
Keep EXPLAIN results with your system documentation. These results can be of value when you reevaluate your database design, and can help you identify and resolve issues that may appear after a system upgrade or migration.
EXPLAIN Request Modifier Processes SQL Requests Only
You can modify any valid Teradata SQL request with EXPLAIN. You cannot EXPLAIN a USING request modifier, another EXPLAIN request modifier, or individual functions, stored procedures, or methods.
Effect of Parameterized Data Parcel Values Cache Peeking on EXPLAIN Reports
If you specify USING data, or if you specify a DATE, CURRENT_DATE, CURRENT_TIMESTAMP, or USER built-in function in a request, or both, the system can invoke Request Cache peeking (see Parameterized Requests). When this occurs, the EXPLAIN text reports the peeked literal values.
If you do not specify USING data, or if the USING variables, DATE, CURRENT_DATE, CURRENT_TIMESTAMP, or USER values, or both are not peeked, then there is no impact on either the generated plan or the generated EXPLAIN text.
- EXPLAIN
- DUMP EXPLAIN
- INSERT EXPLAIN
See Teradata® Call-Level Interface Version 2 Reference for Mainframe-Attached Systems, B035-2417 or Teradata® Call-Level Interface Version 2 Reference for Workstation-Attached Systems, B035-2418 for details about data parcel flavors.
EXPLAIN Text and Conditional Expressions
Conditional expressions are enclosed in APOSTROPHE characters (U+0027). The EXPLAIN text only includes the first 255 characters of conditional expressions.
EXPLAIN Text and Session Character Sets
The database returns EXPLAIN text literals and object names that include characters not in the repertoire of the character set or that are otherwise unprintable as Unicode delimited literals or identifiers. The database only escapes those characters that are outside the repertoire to hexadecimal.
The rules for handling such characters and the escape character usage are as follows:
- The database uses BACKSLASH (U+005C) as the escape character if is available in the session character set.
- Otherwise, the database uses the YEN sign (U+00A5) or the WON sign (U+20A9) as the escape character if is present in the session character set at 0x5C.
- If none of those conditions is true, the database uses NUMBER SIGN (U+0023) as the escape character. This character is required in all session character sets supported by Teradata.
- Vantage adds double quotation marks around object names that are translatable, but not lexically distinguishable as names.
EXPLAIN and Teradata Viewpoint
EXPLAIN reports can be viewed in the Teradata Manager SQL Scratchpad portlet of Viewpoint. To view a summarized version of the EXPLAIN output for an executing request, use the Teradata Viewpoint Query Monitor portlet.
To view the EXPLAIN output for a request run through Teradata Viewpoint, use the Teradata Viewpoint SQL Scratchpad portlet.