DUMP EXPLAIN | SQL Statements | VantageCloud Lake - DUMP EXPLAIN - Teradata Vantage

Teradata® VantageCloud Lake
Deployment
VantageCloud
Edition
Lake
Product
Teradata Vantage
Published
January 2023
ft:locale
en-US
ft:lastEdition
2024-12-11
dita:mapPath
phg1621910019905.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
phg1621910019905
This statement is only supported on the Block File System on the primary cluster. It is not available for the Object File System.

Returns the Optimizer plan information for a query to the requestor in the form of a script containing a series of INSERT requests.

For more information about index analysis, see:
    INITIATE INDEX ANALYSIS and INSERT EXPLAIN are supported only on the Block File System on the primary cluster, not the Object File System.

    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

    Typically invoked using client-based database query analysis tools.

    DUMP EXPLAIN Actions without an Existing QCD

    Make sure 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 different machines and want to make sure 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 can submit the DUMP requests as part of a batch job during a less burdened workload period.

    DUMP EXPLAIN Is Not Valid in a Multiple-Statement Request

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

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

    The following DUMP EXPLAIN request captures the query plan for the next multiple-statement request and is not part of that multiple-statement request. Therefore, 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 may 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, and is therefore generic.