COLLECT STATISTICS (QCD Form) - Teradata Database

SQL Data Manipulation Language
Product
Teradata Database
Release Number
16.10
Published
June 2017
Language
English (United States)
Last Update
2018-04-25
dita:mapPath
psg1480972718197.ditamap
dita:ditavalPath
changebar_rev_16_10_exclude_audience_ie.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata® Database

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 various database query analysis tools: they are not used by the Optimizer to process queries. For information on COLLECT STATISTICS (Optimizer Form), see SQL Data Definition Language - Syntax and Examples, B035-1144.

For more information about index analysis, see:
For more information about client-based query analysis tools, see:
  • Teradata Index Wizard User Guide
  • Teradata Viewpoint User Guide, chapter on the Stats Manager

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.
Note that 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 do 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 given 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 given 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.