DUMP EXPLAIN | SQL Statements | Teradata Vantage - DUMP EXPLAIN - Advanced SQL Engine - Teradata Database

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.

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. In this case, 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;

  *** 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.