INSERT EXPLAIN - Teradata Database

Purpose

Captures the Optimizer white tree information for a query and writes it to a user-defined query capture database, optionally as an XML document.

Required Privileges

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.

Syntax

Syntax Elements

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””.

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”.

If you do not specify this option, then the system does not collect table-level demographics.

FOR table_name

FOR database_name.table_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:

• WITH STATISTICS, Teradata Database collects statistics for all tables referenced by the query.
• WITH STATISTICS … FOR table_name, Teradata Database collects statistics only for the tables referenced by the query that are also specified in the FOR table_name clause.
• WITH NO STATISTICS … FOR table_name, Teradata Database collects statistics only for the tables referenced by the query that are not listed in the FOR table_name clause.

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:

• USING SAMPLE
• FOR table_name
• FOR frequency

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:

• CHECK STATISTICS
• WITH STATISTICS

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.

• DELETE
• EXEC (Macro Form)
• INSERT
• MERGE
• SELECT
• UPDATE

ANSI Compliance

INSERT EXPLAIN is a Teradata extension to the ANSI SQL:2011 standard.