Usage Notes - Advanced SQL Engine - Teradata Database

SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
qtb1554762060450.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata Vantage™

Invocation

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. Use the Control Center feature of the Visual Explain tool to create your QCD databases. For more information, see Teradata® Visual Explain User Guide, B035-2504.

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 “Query Capture Facility” in Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142. This information is critical to the index recommendation analyses performed by the Teradata Index Wizard.

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 “COLLECT STATISTICS (Optimizer Form)” in 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. This information is used by the Teradata Index Wizard utility to determine the benefit of including various columns as indexes.

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.

If you are collecting statistics for use by the Teradata Index Wizard utility, then INSERT EXPLAIN performs the following actions in the order indicated:

  1. Runs an EXPLAIN (see EXPLAIN Request Modifier) on the SQL DML request specified by SQL_statement to generate an Optimizer white tree.
  2. Captures the Optimizer white tree output for that EXPLAIN.
  3. Captures the table cardinality and column statistics for the tables referenced by SQL_request, excluding any tables explicitly excluded by the FOR table_name clause. The columns are those identified as potential index candidates.
  4. Captures the number of times SQL_request is performed in the identified workload as specified by the FOR frequency clause.
  5. Writes the output to the appropriate tables in the user-specified query capture database.

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.

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

The Teradata Index Wizard internally generates plans for workload queries for the purpose of estimating 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.

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.
BEGIN QUERY LOGGING … XMLPLAN INSERT EXPLAIN description
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.