EXPLAIN Request Modifier | Interpreting EXPLAIN Output | Teradata Vantage - EXPLAIN Request Modifier - Advanced SQL Engine - Teradata Database

Overview

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 would 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 would 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 would be 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.

Although the times reported in the EXPLAIN output are presented in units of seconds, they are actually arbitrary units of time, intended to allow you to compare the relative performance of alternate coding formulations of the same query.

The plan for a request as ultimately executed 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. For more information on DBQL, see Teradata Vantage™ - Database Administration, B035-1093.

Keep EXPLAIN results with your system documentation because they 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.

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:

1. If BACKSLASH (U+005C) is available in the session character set, the database uses it as the escape character.
2. Otherwise, if the YEN sign (U+00A5) or the WON sign (U+20A9) is present in the session character set at 0x5C, the database uses it as the escape character.
3. 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.
4. Teradata Database 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 executed through Teradata Viewpoint, use the Teradata Viewpoint SQL Scratchpad portlet.

Teradata Visual Explain

The Teradata Visual Explain tool provides a graphic display of Optimizer plans and also permits you to compare the plans for queries that return the same result. This feature can simplify the interpretation of EXPLAIN reports.

You cannot view Teradata Visual Explain reports using Teradata Viewpoint.

For more information about Teradata Visual Explain, see Query Rewrite, Statistics, and Optimization, and Teradata® Visual Explain User Guide, B035-2504.