INSERT EXPLAIN Syntax | SQL Statements | Teradata Vantage - INSERT EXPLAIN Syntax - Advanced SQL Engine - Teradata Database

INSERT EXPLAIN
  [ WITH [NO] STATISTICS [ USING SAMPLE percentage [ PERCENT ] ] ]
  [ AND DEMOGRAPHICS ]
  [ FOR for_spec [,...] ]
  INTO QCD_name
  [ AS query_plan_name ]
  [ LIMIT [ SQL [= n] ] ]
  [ FOR frequency ]
  [ CHECK STATISTICS ]
  [ IN XML [NODDLTEXT] ]
  SQL_request [;]

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.

Specifying this option is equivalent to performing COLLECT STATISTICS (QCD Form) on the query table columns.

WITH NO STATISTICS

Do not capture statistics for the WHERE clause condition columns specified in SQL_statement.

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.

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_spec

[ database_name. | user_name. ] table_name

Set of tables for which data demographics and statistics details are either to be included or excluded for analysis, 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, the database collects statistics for all tables referenced by the query.
• WITH STATISTICS FOR table_name, the 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, the 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.

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.

query_plan_name

Optional user-defined name under which the query plan information is to be stored. For information on object naming, see Teradata Vantage™ - SQL Fundamentals, B035-1141.

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, various query plans within a given database can be distinguished. see Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142. 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 [ 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.

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 Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142.

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