DUMP EXPLAIN | SQL Statements | Teradata Vantage - DUMP EXPLAIN - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™

Captures the Optimizer plan information for a query and returns it to the requestor in the form of a script containing a series of INSERT requests.

For more information about index analysis, see:

ANSI Compliance

DUMP EXPLAIN is a Teradata extension to the ANSI SQL:2011 standard.

Required Privileges

You must have INSERT privileges on all the tables in the specified query capture database.

Invocation

Normally invoked using client-based database query analysis tools.

DUMP EXPLAIN Actions Without An Existing QCD

Ensure that there is an existing QCD_name database on the test system before running the script produced by DUMP EXPLAIN for the first time.

If a QCD_name database or any of its tables does not exist, or if you do not have INSERT privileges on one or more QCD_name database tables when you perform the script produced by DUMP EXPLAIN, then the system displays an appropriate error message and terminates performance of the script.

Actions Performed by DUMP EXPLAIN

DUMP EXPLAIN performs the following actions in the order indicated.

  1. Runs an EXPLAIN on the SQL DML request specified by SQL_statement.
  2. Captures the Optimizer plan output of that EXPLAIN.
  3. Returns the output to the requestor as a script containing a series of INSERT requests designed to be used to update the appropriate tables in the user-specified query capture database.

Use DUMP EXPLAIN rather than INSERT EXPLAIN if you are collecting information from several different machines and you want to ensure that only the selected QCD on the appropriate machine is updated with the results or if you do not want to update the QCD during heavy workload periods. You could submit the DUMP requests as part of a batch job during a less burdened workload period.

DUMP EXPLAIN Is Not Valid Within a Multistatement Request

You cannot submit an DUMP EXPLAIN request as part of a multistatement request, though you can submit an DUMP EXPLAIN request for a multistatement request.

If you attempt to submit a multistatement request that contains an DUMP EXPLAIN request, the multistatement request aborts and returns an error.

Note that while the DUMP EXPLAIN request in the following example superficially appears to be a valid statement in a multistatement request, it actually captures the query plan for a multistatement request that follows it, and is not itself part of that multistatement request. As a result, the request is treated like any other DUMP EXPLAIN and completes successfully.

DUMP EXPLAIN INTO qcd SELECT * FROM d1.t1
;SELECT * FROM d1.t1;

Result:

  *** Insert completed. One row added.
  *** Total elapsed time was 1 second.

DUMP EXPLAIN Does Not Support Capturing Output in XML

Unlike INSERT EXPLAIN and the EXPLAIN request modifier, the DUMP EXPLAIN statement does not support the capture of query plan data as an XML document.

Effects of Request Cache Peeking on DUMP EXPLAIN Outcomes

When a data parcel is submitted with an DUMP EXPLAIN request, the plan might be generated with peeked USING and CURRENT_DATE or DATE values, or both. If any of these values are peeked, then the query plan shows them.

If no data parcel is submitted with an DUMP EXPLAIN request, the resulting plan is generated without peeking at USING or CURRENT_DATE, or DATE values, so it is a generic plan by definition. Note that the Teradata System Emulation Tool does not accept USING data as input while capturing query plans using DUMP EXPLAIN requests unless those requests are submitted using BTEQ.