Captures the Optimizer white tree information for a query and writes it to a user-defined query capture database, optionally as an XML document.
where:
Syntax element … |
Specifies … |
WITH STATISTICS |
Capture the statistics for the WHERE clause condition columns specified in SQL_statement. You cannot specify WITH STATISTICS if you also specify the IN XML option. You cannot specify the WITH STATISTICS option if you submit an INSERT EXPLAIN request in Index Validation mode (see “DIAGNOSTIC “Validate Index”” on page 623). Specifying this option is equivalent to performing COLLECT STATISTICS (QCD Form) on the query table columns. The columns inserted into the QCD are those determined to be index candidates by the Teradata Index Wizard. |
USING SAMPLE percentage USING SAMPLE percentage PERCENT |
Percentage of data to be read for collecting statistics. If you do not specify this clause, the system automatically selects a sample size based on the cardinality of the table. You cannot specify a USING SAMPLE clause if you also specify an IN XML clause. The valid range is between 1 and 100, inclusive. |
WITH NO STATISTICS |
Do not capture statistics for the WHERE clause condition columns specified in SQL_statement. |
AND DEMOGRAPHICS |
Collect table‑level demographics as well as statistics. Specifying this option is equivalent to performing a COLLECT DEMOGRAPHICS request on the query tables. See “COLLECT DEMOGRAPHICS” on page 534. If you do not specify this option, then the system does not collect table‑level demographics. |
FOR table_name FOR database_name. FOR user_name.table_name |
Set of tables for which data demographics and statistics details are either to be included or excluded for analysis by the Teradata Index Wizard, depending on whether you specify the WITH STATISTICS option or the WITH NO STATISTICS options, respectively. Statistics are collected on columns that are index candidates and for all columns and indexes referenced explicitly with values in SQL_statement for the following cases. A candidate index column is defined as a column for which the value or join range access frequencies stored in the Field table of the specified QCD are greater than 0. If you specify: You cannot specify a FOR table_name clause if you also specify an IN XML clause. |
INTO QCD_name |
User-defined query capture database. A database named QCD_name must exist on the system. Otherwise, the request aborts and returns an error. Using the Control Center feature of the Visual Explain tool is the easiest way to create your QCD databases. |
AS query_plan_name |
Optional user-defined name under which the query plan information is to be stored. For information on object naming, see SQL Fundamentals. If no query_plan_name is specified, the query plan information is stored with a null name. Because each query plan is stored with a unique non-null Query ID (see SQL Request and Transaction Processing), various query plans within a given database can be distinguished. Note that Query IDs are not unique across databases. query_plan_name is not constrained to be unique. You can store query_plan_name as query plan name if you enclose the pad character-separated words in APOSTROPHE characters as “query plan name”. |
LIMIT LIMIT SQL LIMIT SQL =n |
Limit the size of the query, DDL, and view text captured for the QCD tables Query, Relation, and ViewText, respectively. If you do not specify this clause, then the system captures complete SQL text. The value for n indicates the upper limit on the amount of text to capture. If you specify either LIMIT by itself or LIMIT SQL without a value, then the clause is equivalent to LIMIT SQL = 0, and no text is captured. |
FOR frequency |
Number of times SQL_statement is typically performed within its identified workload. This value is used to weight the respective benefits of each column analyzed for inclusion in the index recommendation computed by the Teradata Index Wizard utility. Any positive integer up to 4 bytes is valid. You cannot specify a FOR frequency clause if you also specify an IN XML clause. If this clause is not specified, then the value for frequency defaults to 1. |
CHECK STATISTICS |
Capture COLLECT STATISTICS recommendations for SQL_statement in the StatsRecs QCD table. See SQL Request and Transaction Processing. You cannot specify CHECK STATISTICS if you also specify the IN XML option. |
IN XML |
Capture the output as an XML document. This document is stored in the QCD table named XMLQCD. You cannot specify an IN XML clause if you also specify any of the following options: If you specify the IN XML option, you can also specify the NODDLTEXT option. If you specify the IN XML option, you cannot specify any of the following options with it: |
NODDLTEXT |
Do not capture the DDL text in the XML document stored in qcd.XMLQCD. |
SQL_request |
DML request whose Optimizer white tree information is to be captured and written to the QCD. SQL_request is limited to the following statements. |
INSERT EXPLAIN is a Teradata extension to the ANSI SQL:2011 standard.
You must have INSERT privileges on the tables in the user-defined query capture database.
You must also have the SELECT privilege on all referenced tables to capture data demographics and column statistics.
Normally invoked using client‑based database query analysis tools.
The following rules apply to the use of INSERT EXPLAIN:
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.
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.
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 SQL Request and Transaction Processing. This information is critical to the index recommendation analyses performed by the Teradata Index Wizard.
Note that 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 SQL Data Definition Language.
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.
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.
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.
IF you specify this option … |
THEN INSERT EXPLAIN also … |
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 page 552) 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.