[ WITH [NO] STATISTICS [ USING SAMPLE percentage [ PERCENT ] ] ]
[ AND DEMOGRAPHICS ]
[ FOR for_spec [,...] ]
[ AS query_plan_name ]
[ LIMIT [ SQL [= n] ] ]
[ FOR frequency ]
[ CHECK STATISTICS ]
[ IN XML [NODDLTEXT] ]
- 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.
- The columns inserted into the QCD are those determined to be index candidates by the Teradata Index Wizard.
- 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.
[ 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 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, 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.
- 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.
- 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 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
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
- Do not capture the DDL text in the XML document stored in qcd.XMLQCD.
- DML request whose Optimizer white tree information is to be captured and written to the QCD.
SQL_request is limited to the following statements.
- EXEC (Macro Form)