COLLECT STATISTICS Syntax | SQL Statements | Teradata Vantage - COLLECT STATISTICS Syntax (QCD Form) - Advanced SQL Engine - Teradata Database

COLLECT { STATISTICS | STATS | STAT }
  FOR SAMPLE percentage [PERCENT]
  INTO QCD_name
  [ SET QUERY query_ID ]
  [ SAMPLEID statistics_ID ]
  [ UPDATE MODIFIED ]
  [ON] [ database_name. | user_name. ] table_name

  { COLUMN { { column_name | PARTITION } |
             ( { column_name | PARTITION } [,...] )
           } |

    INDEX { index_name | ( column_name [,...] ) }
  } [;]

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.