EXPLAIN Request Modifier | VantageCloud Lake - EXPLAIN Request Modifier - Teradata VantageCloud Lake

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 EXPLAIN Request Modifier.

EXPLAIN is a 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.

The times reported in the EXPLAIN output are presented in units of seconds, but are 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 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 DBQL Overview .

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