INSERT EXPLAIN Usage Notes - Teradata VantageCloud Lake

Invocation

Typically invoked using client-based database query analysis tools.

Rules for Using INSERT EXPLAIN

INSERT EXPLAIN Actions without an Existing QCD

Make sure that there is an existing QCD_name database before running INSERT EXPLAIN for the first time.

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

Creating Index Analysis Workloads Using the WITH STATISTICS and FOR frequency Options

When you specify the WITH STATISTICS option with INSERT EXPLAIN, sample table cardinality estimates and column statistics are captured in the TableStatistics tables of your QCD databases. The confidence the Optimizer has in its index recommendations is expressed in the Level column of the QCD table StatsRecs.

The sampled statistics captured using the WITH STATISTICS clause are identical to those captured by performing a COLLECT STATISTICS Syntax (QCD Form) request and a COLLECT request.

The FOR frequency clause provides an estimate of the average number of times the SQL request being analyzed is typically performed during the submitted workload.

Collecting New Statistics When You Specify INSERT EXPLAIN WITH STATISTICS

INSERT EXPLAIN WITH STATISTICS collects fresh, sampled statistics only if there are no statistics for the candidate index column set in DBC.TVFields for table_name. Otherwise, the existing statistics, whether based on a sample or not, are copied to the QCD and are not recollected. This enables the index analysis process to make full use of nonsampled statistics if available.

Actions Performed by INSERT EXPLAIN

You cannot specify either of these options if you also specify the IN XML option.

INSERT EXPLAIN Is Not Valid within a Multiple-Statement Request

You cannot submit an INSERT EXPLAIN request as part of a multiple-statement request, though you can submit an INSERT EXPLAIN request for a multiple-statement request. If you attempt to submit a multiple-statement request that contains an INSERT EXPLAIN request, the multiple-statement request returns an error.

While the INSERT EXPLAIN request in the following example appears to be a valid statement in a multiple-statement request, request captures the query plan for a multiple-statement request that follows it, and is not itself part of that multiple-statement request. Therefore, the request is treated like any other INSERT EXPLAIN and completes successfully.

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

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

Effects of Data Parcel Peeking on INSERT EXPLAIN Outcomes

When a data parcel is submitted with an INSERT EXPLAIN request, the plan may be generated with peeking at 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 INSERT EXPLAIN request, the resulting plan is generated without peeking at USING, CURRENT_DATE, or DATE values, and is therefore generic.

Capturing the INSERT EXPLAIN Output as an XML Document

The IN XML option for INSERT EXPLAIN provides a functionally equivalent representation of a query plan created by an INSERT EXPLAIN request that does not produce XML output.

INSERT EXPLAIN operations that require minutes to process when you do not specify IN XML take seconds to complete when you capture the information as an XML file. This is because an INSERT EXPLAIN IN XML request performs only one insert operation by storing the entire output of the request as one or more 31,000 byte slices of a single XML file. When inserting XML data into the QCD table XMLQCD, INSERT EXPLAIN also updates the QCD table SeqNumber, but updating SeqNumber is trivial.

When you specify the IN XML option, the database does not update any other QCD tables beyond XMLQCD and SeqNumber. This is unlike a standard INSERT EXPLAIN request where inserts must be made into multiple QCD tables.

In a single QCD table, XMLQCD has a marked performance advantage over a standard INSERT EXPLAIN QCD data collection operation.

A similar option for the BEGIN QUERY LOGGING and REPLACE QUERY LOGGING statements optionally creates a compact XML version of QCD information that can be stored in a single DBQL table, DBC.DBQLXMLTbl, making the features complementary.

XMLPLAN query logging is not an alternative method for capturing the information captured by INSERT EXPLAIN requests. These two produce documents with different content and have an important difference in query execution.

XMLPLAN logging is ideal when you want to record query plans for your executing workloads and using INSERT EXPLAIN requests to capture query plans for those workloads is too slow for your needs.

If you are only tuning a query and do not want to run it, XMLPLAN logging is not as useful as capturing the query plan for a request using INSERT EXPLAIN requests.