EXPLAIN Request Modifier
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 processed, except in the case of a dynamic plan where the request may be partially processed.
Optionally returns the output as XML text instead of plain English text.
Syntax
where:
|
Syntax element … |
Specifies … |
|
STATIC |
that 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 unless the cost profile constant ExplainMode is changed to make the default DYNAMIC . |
|
DYNAMIC |
that an EXPLAIN for the plan is generated. If the Optimizer determines that the request is: For information about DBS Control, see “DBS Control (dbscontrol)” in Utilities. Optimizer cost profile constants can override DBS Control settings. For information about setting cost profiles, see “CopyCostProfile,” “InsertConstantValue,” and “DeleteCostProfile” in Data Dictionary. The following Cost Profile constants apply to IPE: The DynamicPlan and SpecificPlan query band settings can also determine when the Optimizer uses IPE. See “SET QUERY_BAND” in SQL Data Definition Language Detailed Topics and SQL Data Definition Language Syntax and Examples. |
|
IN XML |
to return the output as XML text rather than as plain English text. For more information, see SQL Data Definition Language. |
|
NO DDLTEXT |
not to capture the DDL text in the XML EXPLAIN text. |
|
SQL_request |
the 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 the following:
Required Privileges
To EXPLAIN a request, you must have the permissions that are required to execute that request.
The Optimizer processes an explained request the same as the request would be processed without being modified by the EXPLAIN, except for actually executing the SQL within the request.
EXPLAIN and the USING Request Modifier
If you specify both an EXPLAIN request modifier and a USING request modifier (see “USING Request Modifier” on page 530) for the same request, the EXPLAIN modifier must precede the USING modifier. Note that 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 SQL Request and Transaction Processing for the XML plan attributes and the XML schema used by the XML output produced by the IN XML option.
The EXPLAIN request modifier can also specify that the output be returned in one of four possible formats:
You specify this with the IN XML option.
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 SQL Data Definition Language.
EXPLAIN Report Overview
EXPLAIN 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 SQL_request: the report details which indexes would be used to process the request, identifies any intermediate spool files 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.
References to bit mapping might appear when complex conditional expressions involving nonunique secondary indexes are applied to a very large table.
Such expressions can be resolved by mapping each subtable row ID to a number in the range 0 through 32,767. This number is used as an index into a bit map in which the bit for each qualifying data row is set equal to 1.
The Optimizer is better able to determine whether the table is a candidate for bit mapping when up-to-date statistics exist for the important columns of that table. See SQL Data Definition Language.
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 their relative performance. They do not correlate with clock time.
Save 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 repertoire of 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 it is available, as the escape character. If BACKSLASH is not available:
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 SQL Fundamentals.
EXPLAIN Processes SQL Requests Only
You can modify any valid Teradata SQL request with EXPLAIN. The definition of what is not an SQL request is important here. For example, you cannot EXPLAIN a USING request modifier (see “USING Request Modifier” on page 530), a WITH RECURSIVE view modifier (see “WITH Statement Modifier” on page 60), or another EXPLAIN modifier. Nor can you explain individual functions, stored 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. Related supporting information appears in SQL Request and Transaction Processing.
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 SQL Stored Procedures and Embedded SQL, 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 Stored Procedures
You cannot EXPLAIN compiled stored procedures.
Do use EXPLAIN while building your stored procedures. To do this, you must extract the SQL text from the procedure body and EXPLAIN the individual statements by themselves. This might require modification of the stored procedure SQL text in some cases. For example, you might have to remove an INTO clause or add a USING request modifier to represent stored procedure variables and parameters.
EXPLAIN works by parsing and optimizing SQL source code text just as it would be parsed and optimized if it were submitted by a client application for processing. You EXPLAIN a string of SQL text, not the name of an object that contains strings of SQL text. Once a stored procedure has been compiled, it is a database object and it can no longer be processed using an EXPLAIN modifier as if it were simple SQL text.
For example, if you compile a stored procedure under the name update_orders and then perform the following EXPLAIN, it will fail because update_orders is not a valid SQL statement.
EXPLAIN update_orders;
While you are developing the SQL requests that constitute the UpdateOrders procedure, however, you should always use EXPLAINs to help you to write the most efficient SQL code possible.
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 SQL Request and Transaction Processing. 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:
For details about data parcel formats, see Teradata Call-Level Interface Version 2 Reference for Mainframe-Attached Systems or Teradata Call-Level Interface Version 2 Reference for Workstation-Attached Systems.
Standard Form of Display for EXPLAIN
The standard output of an EXPLAIN request modifier displays character constant strings as follows:
|
FOR this type of constant … |
The value is displayed in this format … |
|
printable single byte character |
Teradata Latin |
|
anything else |
internal Teradata hexadecimal. |
Using EXPLAIN to Determine All Database Objects 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 all the objects a particular view accesses, including any nested views, use the SHOW statement. See “SHOW” in SQL Data Definition Language.
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.
Example
The query plan generated for the following request is captured and returned to the requestor as XML text:
EXPLAIN IN XML
SELECT *
FROM DBC.DBCInfoV;
Example
The query plan generated for the following request is captured and returned to the requestor as XML text. Because the request specifies the NODDLTEXT option, all DDL‑related text is suppressed.
EXPLAIN IN XML NODDLTEXT
SELECT *
FROM DBC.DBCInfoV;
Example
The query plan generated for the following request is captured and returned to the requestor as XML text.
EXPLAIN IN XML NODDLTEXT
SELECT *
FROM DBC.DBCInfoV;
Related Topics
See the following for additional information about how to interpret the reports generated by an EXPLAIN request modifier or its related Visual Explain utility: