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:
- COLLECT DEMOGRAPHICS
- “INITIATE INDEX ANALYSIS”
- INSERT EXPLAIN
- “RESTART INDEX ANALYSIS”
- SQL Request and Transaction Processing
- “DROP STATISTICS (QCD Form)”
- “HELP STATISTICS (Optimizer Form)” in SQL Data Definition Language - Detailed Topics , B035-1184
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.
- INTO QCD_name
- Name of the QCD database into which the collected sampled statistics are to be written.
- SET QUERY query_ID
- Value inserted into QCD.TableStatistics.QueryId.
- SAMPLEID statistics_ID
- Value to be inserted into QCD.TableStatistics.StatisticsId.
- UPDATE MODIFIED
- Store modified statistics stored in QCD.TableStatistics.ModifiedStats.
- 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.
- COLUMN column_name
- Name of a column set on which sampled statistics are to be collected.
- 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.
- 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.