COLLECT STATISTICS (QCD Form) - Teradata Database - Teradata Vantage NewSQL Engine

SQL Data Manipulation Language

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Language
English (United States)
Last Update
2019-05-03
dita:mapPath
fbo1512081269404.ditamap
dita:ditavalPath
TD_DBS_16_20_Update1.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata Vantage™

Purpose

Collects demographic data for one or more columns and indexes of a table, computes a statistical profile of the collected data, and stores the synopsis in the TableStatistics table of the specified QCD database.

Statistics collected by this statement are used for index analysis and validation tasks performed by database query analysis tools. These statistics are not used by the Optimizer to process queries. For information on COLLECT STATISTICS (Optimizer Form), see Teradata Vantage™ SQL Data Definition Language Detailed Topics , B035-1184 .

For more information about index analysis, see:
For more information about client-based query analysis tools, see:
  • Teradata® Index Wizard User Guide, B035-2506
  • Teradata® Viewpoint User Guide, B035-2206, Stats Manager topic

Required Privileges

You must have the following privileges to perform COLLECT STATISTICS (QCD Form):
  • INDEX or DROP TABLE on table_name or its containing database.
  • INSERT and UPDATE on the TableStatistics table or its containing QCD database.

Syntax



Syntax Elements

FOR SAMPLE percentage
FOR SAMPLE percentage PERCENT
Percentage of table rows to be sampled to build the statistics for the specified column and index sets. The percentage value must be less than 100.
For example, if you specify a value of 5, then 5 percent of the rows for table_name on each AMP are read to collect their statistics.
This option is not recognized for single-column statistics requests for the PARTITION column of a row-partitioned table. The system automatically increases the percentage to 100.
The restriction for COLLECT STATISTICS (Optimizer Form) on using sampling with columns of a row-partitioned table that are also members of the partitioning expression for that table does not apply to COLLECT STATISTICS (QCD Form).
PERCENT is an optional keyword to indicate that the value specified for percentage is a percentage.
INTO QCD_name
Name of the QCD database into which the collected sampled statistics are to be written.
The sampled statistics are written to the TableStatistics table of the specified QCD database.
SET QUERY query_ID
Value inserted into QCD.TableStatistics.QueryId.
Each unique composite of query_ID and statistics_ID enables you to store a separate set of statistics in QCD.TableStatistics for a particular column or index.
The default value is 0.
SAMPLEID statistics_ID
Value to be inserted into QCD.TableStatistics.StatisticsId.
Each unique composite of query_ID and statistics_ID enables you to store a separate set of statistics in QCD.TableStatistics for a particular column or index.
The default value is 1.
UPDATE MODIFIED
Store modified statistics stored in QCD.TableStatistics.ModifiedStats.
If you do not specify the UPDATE MODIFIED option, the system stores unmodified statistics in QCD.TableStatistics.StatisticsInfo.
database_name user_name
Name of the containing database or user for table_name if different from the current database or user.
table_name
Name of the table for which the specified column and index sampled statistics and sample size are to be collected.
You cannot collect QCD statistics on volatile, journal, global temporary, or global temporary trace tables.
COLUMN column_name
Name of a column set on which sampled statistics are to be collected.
You can collect statistics on both a column set and on the system-derived PARTITION column in the same COLLECT STATISTICS request.
You cannot collect statistics on UDT or LOB columns.
COLUMN PARTITION
Statistics are to be collected on the system-derived PARTITION column for a table. The value collected is the system-derived partition number, which ranges from 1 through 65,535 inclusive, or is zero for non-partitioned.
You can collect statistics on both a column set and on the system-derived PARTITION column in the same COLLECT STATISTICS request.
Even though table_name need not identify a partitioned table, and the system does not return an error if you request PARTITION column statistics on a nonpartitioned table, the operation serves no purpose.
You cannot collect PARTITION statistics on the following:
  • Join indexes
  • Global temporary tables
  • Volatile tables
INDEX index_name
Name of the index on which sampled statistics are to be collected.
INDEX column_name
Names of the columns in the column set on which sampled statistics are to be collected.

ANSI Compliance

COLLECT STATISTICS (QCD Form) is a Teradata extension to the ANSI SQL:2011 standard.