Usage Notes - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Analytics Database
Teradata Vantage
Release Number
June 2022
English (United States)
Last Update
Product Category
Teradata Vantage™


Normally invoked using client-based database query analysis tools.

Rules for Using INSERT EXPLAIN

The following rules apply to the use of INSERT EXPLAIN:
  • When you specify a table list, but none of the tables referenced by SQL_statement are in the specified table list, an error is returned.
  • When you specify a table list, but not all of the tables referenced by SQL_statement are in the specified table list, no error is returned, but the nonmatching tables are ignored.

INSERT EXPLAIN Actions Without An Existing QCD

Ensure 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. For details about the StatsRec table, see the information about the Query Capture Facility in Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142.

The sampled statistics captured using the WITH STATISTICS clause are identical to those captured by performing a COLLECT STATISTICS (QCD form) request and a COLLECT request. For more information, see Teradata Vantage™ - SQL Data Definition Language Detailed Topics, B035-1184.

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 they are available.

Actions Performed by INSERT EXPLAIN

INSERT EXPLAIN performs the following actions in the order indicated:
  1. Runs an EXPLAIN on the SQL DML request specified by SQL_statement to generate an Optimizer white tree.
  2. Captures the Optimizer white tree output of that EXPLAIN.
  3. Writes the output to the appropriate tables in the user-specified query capture database.
Option Description
USING SAMPLE Collects statistics on the specified sample size from the population rather than allowing the system to select a sample size based on the cardinality of the table.
CHECK STATISTICS Generates a set of recommended statistics to collect and stores the recommendations in the StatsRec QCD table.

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

INSERT EXPLAIN Is Not Valid Within a Multistatement Request

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

While the INSERT EXPLAIN request in the following example appears to be a valid statement in a multistatement request, request 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 INSERT EXPLAIN and completes successfully.

;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 might 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, 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 INSERT EXPLAIN requests unless those requests are submitted using BTEQ.

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 several minutes to be processed when you do not specify the IN XML option take only a few 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 INSERT EXPLAIN inserts XML data into the QCD table XMLQCD, it also updates the QCD table SeqNumber, but the update of SeqNumber is a trivial operation.

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. For the definition of the XMLQCD table, see Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142

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 to one another.

XMLPLAN query logging is not an alternative method for capturing the information captured by INSERT EXPLAIN requests.
  • In addition to differences in the content of the documents produced, these two methods have an important difference regarding the execution of the query.
Logs query plans for executed queries. captures query plans without executing the query.

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 execute it, XMLPLAN logging is not as useful as capturing the query plan for a request using INSERT EXPLAIN requests.

In this case, executing an INSERT EXPLAIN INTO QCD_name IN XML request or an EXPLAIN IN XML SQL_request is a more viable alternative. See Capturing EXPLAIN Text Output in XML Format.
  • Runtime information from the traditional DBQL tables is also captured for a logged plan.
  • XMLPLAN logging is more of an extension to query logging than an extension to the Query Capture Facility.