16.10 - EXPLAIN Request Modifier - Teradata Database

Teradata Database SQL Request and Transaction Processing

prodname
Teradata Database
vrm_release
16.10
created_date
June 2017
category
Programming Reference
User Guide
featnum
B035-1142-161K

EXPLAIN Report Overview

An EXPLAIN request modifier reports a summary of the query plan generated by the SQL query optimizer to process any valid SQL request: the steps the system would perform to resolve a request.

The EXPLAIN modifier is an extremely useful utility for query designers because it provides an English language summary of the access and join plans generated by the Optimizer for the query you explain with it. The report details which indexes would be used to process the request, identifies any intermediate spool that would be generated, indicates the types of join to be performed, shows whether the requests in a transaction would be dispatched in parallel, and so on.

When you perform an EXPLAIN against any SQL request, that request is parsed and optimized, and the parse tree (access and join plans) generated by the Optimizer is returned to the requestor in the form of a text file that explains the steps taken in the optimization of the request as well as the relative time it would take to complete the request given the statistics the Optimizer had to work with. The EXPLAIN report reproduces the execution strategy of the Optimizer, but does not explain why it makes the choices it does.

EXPLAIN helps you to evaluate complex queries and to develop alternative, more efficient, processing strategies.

Only the first 255 characters of a conditional expression are displayed in an EXPLAIN. The entire conditional expression is enclosed in APOSTROPHE characters (U+0027).

Although the times reported in the EXPLAIN output are presented in units of seconds, they are actually arbitrary units of time. These numbers are valuable because they permit you to compare alternate coding formulations of the same query with respect to their relative performance.

Keep EXPLAIN results with your system documentation because they can be of value when you reevaluate your database design.

EXPLAIN Request Modifier Processes SQL Requests Only

You can modify any valid Teradata SQL request with EXPLAIN. The definition of what is not a request is important here. For example, you cannot EXPLAIN a USING request modifier (see SQL Data Manipulation Language), a WITH RECURSIVE modifier (see SQL Data Manipulation Language), or another EXPLAIN request modifier. Nor can you explain 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 (see Resolving the DATE or CURRENT_DATE Value for Optimization and Usage Notes for examples).

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.

Note that parameterized requests specified without a USING request modifier, but using either CLIv2 data parcel flavor 3 (Data) or CLIv2 parcel flavor 71 (DataInfo), cannot be explained using any of the following request modifiers or statements:

  • EXPLAIN
  • DUMP EXPLAIN
  • INSERT EXPLAIN

See Teradata Call-Level Interface Version 2 Reference for Mainframe-Attached Systems or Teradata Call-Level Interface Version 2 Reference for Workstation-Attached Systems for details about data parcel flavors.

EXPLAIN Text and Session Character Sets

Teradata 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. Teradata 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, Teradata 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, Teradata Database uses it as the escape character.
  3. If none of those conditions is true, Teradata Database uses NUMBER SIGN (U+0023) as the escape character. This character is required in all session character sets supported by Teradata Database.
  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 Teradata Visual Explain User Guide. Related information appears elsewhere in this chapter and in Query Rewrite and Optimization.