Usage Notes - Teradata Database - Teradata Vantage NewSQL Engine

SQL Data Manipulation Language

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Language
English (United States)
Last Update
2019-05-03
dita:mapPath
fbo1512081269404.ditamap
dita:ditavalPath
TD_DBS_16_20_Update1.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata Vantage™

EXPLAIN and the USING Request Modifier

If you specify both an EXPLAIN request modifier and a USING request modifier for the same request, the EXPLAIN modifier must precede the USING modifier. See USING Request Modifier. A dynamic plan cannot be displayed for requests with a USING request modifier.

Capturing EXPLAIN Text Output in XML Format

The IN XML option returns the EXPLAIN text as XML text rather than as plain English text. See Teradata Vantage™ SQL Request and Transaction Processing, B035-1142 for the XML plan attributes and the XML schema used by the XML output produced by the IN XML option.

The EXPLAIN IN XML request modifier can also specify that the output be returned in one of two possible formats:
  • As XML text with any associated DDL text. You specify this with the IN XML option.
  • As XML text, but without any associated DDL text. You specify this with the IN XML NODDLTEXT option.

A complementary option for the BEGIN QUERY LOGGING and REPLACE QUERY LOGGING statements optionally creates a compact XML version of the information that can be stored in a DBQL table. See “BEGIN QUERY LOGGING” and “REPLACE QUERY LOGGING” in Teradata Vantage™ SQL Data Definition Language Syntax and Examples, B035-1144.

EXPLAIN Report Overview

EXPLAIN provides a summary of the access and join plans generated by the Optimizer for the query SQL requests. The report details which indexes would be used to process the request, identifies any intermediate spools 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 plan generated by the Optimizer is returned to the requestor in the form of a text file that explains the steps of the plan for the request. The EXPLAIN report reproduces the execution strategy determined by 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.

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 relative performance, but do not correlate with clock time. Times and row estimates are not reported for some steps.

Save the EXPLAIN results for future reference.

Substitute Characters in Object Names and Literals Returned in an EXPLAIN Report

If an object name or literal returned in an EXPLAIN report includes a character not in the the session character set, or is otherwise unprintable, the name or literal is shown as a UNICODE delimited identifier or literal.

Each non-representable character is escaped to its hexadecimal equivalent.

The system uses BACKSLASH (U+005C), if available, as the escape character. If BACKSLASH is not available:
  • If either YEN SIGN (U+00A5) or WON SIGN (U+20A9) replaces BACKSLASH in the session character set, the system uses the replacement as the escape character.
  • If none of the preceding characters is available, the system uses NUMBER SIGN (U+0023).

Object names that are translatable to the session character set, but are not lexically distinguishable as names are enclosed in double quotation marks.

For information on object naming, see Teradata Vantage™ SQL Fundamentals, B035-1141.

Standard Form of Display for EXPLAIN

The standard output of an EXPLAIN request modifier displays character constant strings as follows:

Type of Constant Format
Printable single byte character. Teradata Latin.
Anything else. Internal Teradata hexadecimal.

EXPLAIN Processes SQL Requests Only

You can modify any valid Teradata SQL request with EXPLAIN.

You cannot EXPLAIN a USING request modifier, a WITH RECURSIVE view modifier, or another EXPLAIN modifier. See USING Request Modifier and WITH Modifier. You cannot explain individual functions, procedures, or methods.

Teradata Visual Explain Utility

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.

For more information about Teradata Visual Explain, see Teradata® Visual Explain User Guide, B035-2504. Related supporting information appears in Teradata Vantage™ SQL Request and Transaction Processing, B035-1142.

EXPLAIN and Embedded SQL

Use EXPLAIN only with data returning requests. EXPLAIN returns multiple rows of a single column whose data type is VARCHAR(80). Because it causes multiple rows to be returned, EXPLAIN can only be performed with a cursor.

For static execution of EXPLAIN, see Teradata Vantage™ SQL Stored Procedures and Embedded SQL , B035-1148 , substituting an EXPLAIN-modified SQL request for the query_expression clause shown there.

Do not perform EXPLAIN request modifiers dynamically because the result is unpredictable.

EXPLAIN and Procedures

You cannot EXPLAIN procedures.

For example, if you compile the procedure update_orders and perform the following EXPLAIN, an error occurs because update_orders is not a valid SQL statement.

     EXPLAIN update_orders;

You should use EXPLAIN while building your procedures. You must extract the SQL text from the procedure body and EXPLAIN each of the individual statements. This can require modification of the procedure SQL text in some cases. For example, you might have to remove an INTO clause or add a USING request modifier to represent procedure variables and parameters.

Effect of Request Cache Peeking on EXPLAIN Reports

If you specify USING data, or if you specify a DATE or CURRENT_DATE built-in function in a request, or both, the system can invoke Request Cache peeking. See Teradata Vantage™ SQL Request and Transaction Processing, B035-1142. In this case, the EXPLAIN text indicates the peeked literal values.

If you do not specify USING data, or if the USING variables, DATE or CURRENT_DATE 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

For details about data parcel formats, 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.

Using EXPLAIN to Determine the Database Objects that a View Accesses

Explaining a request does not necessarily report the names of all the underlying database objects accessed by that request, but it does provide the names of all the base tables accessed.

To determine the objects that a particular view accesses, including any nested views, use the SHOW statement. See “SHOW” in Teradata Vantage™ SQL Data Definition Language Syntax and Examples, B035-1144.

For example, the following request reports the create text and containing databases for all underlying tables and views accessed by view_name.

     SHOW QUALIFIED SELECT *
     FROM view_name;

On the other hand, the following request reports only the underlying tables accessed by view_name, and not any nested views that are accessed.

     EXPLAIN SELECT *
     FROM view_name;

2PC Session Mode

If you specify an EXPLAIN modifier with a request in 2PC session mode, and that request is followed by a 2PC function, then the SQL portion of the request is explained, but the 2PC function is not.

For example, if the following multistatement request were submitted, the INSERT and SELECT would be explained; the VOTE would not be explained. The VOTE is, however, sent to the AMPs:

     EXPLAIN INSERT tab1(1,2)
            ;SELECT *
             FROM tab1
            ;VOTE

VOTE is specified by the system administrator, not by users submitting requests in 2PC mode.