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

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

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:
  • BEGIN QUERY LOGGING and REPLACE QUERY LOGGING in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144
  • COLLECT DEMOGRAPHICS
  • COLLECT STATISTICS (QCD Form)
  • INITIATE INDEX ANALYSIS
  • INSERT EXPLAIN
  • RESTART INDEX ANALYSIS
  • Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142
  • Teradata Vantage™ - Database Design, B035-1094
  • Teradata® Index Wizard User Guide, B035-2506
  • Teradata® Viewpoint User Guide, B035-2206, Stats Manager topic
  • Teradata® Visual Explain User Guide, B035-2504
  • Teradata® System Emulation Tool User Guide, B035-2492

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. You can use the Control Center feature of the Visual Explain tool to create your QCD databases.

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.

You might want to 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.

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 Visual Explain, Teradata System Emulation Tool, and Teradata Index Wizard do not accept USING data as input while capturing query plans using DUMP EXPLAIN requests unless those requests are submitted using BTEQ or Teradata SQL Assistant.

The Teradata Index Wizard internally generates plans for workload queries in order to estimate workload costs, which are used to determine optimal index recommendations. When queries in the workloads specify USING request modifiers, the plan is generated without peeking at USING, CURRENT_DATE, or DATE values. Because of these factors, Request Cache peeking has no impact on the resulting index. Given that workload analyses should be independent of USING values, this behavior is correct.