Collecting Statistics Using COLUMN and INDEX Options | Teradata Vantage - 17.10 - Collecting Statistics When No COLUMN or INDEX Clause Is Specified - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1184-171K
Language
English (United States)

If you do not specify a COLUMN or INDEX clause, a COLLECT STATISTICS request updates the statistics for all columns and indexes for which statistics had previously been collected. This is called recollecting statistics.

The exceptions are as follows:
  • Statistics that have been deleted by a DROP STATISTICS request are not updated.

    To replace deleted statistics, specify the appropriate set of column or index names in a COLLECT STATISTICS request.

  • Statistics can be recollected on a combined maximum of 512 implicitly specified columns and indexes. The system returns an error if statistics have been collected for more than 512 columns and indexes in the table.

    This limit can be lower than 512 columns subject to limits on the other resources such as the number of available spools and plastic or concrete step segment sizes.

  • The system recollects statistics in the same mode (full file scan, sampled, or cylinder index scan) that you specified when the information was originally collected.

    A cylinder index scan performs up to n-1 data block reads, where n is the number of partitions, when collecting statistics on the system-derived PARTITION column of a table.

  • You cannot specify an explicit USING SAMPLE clause to recollect statistics.

    If the original statistics were collected using sampling, then the recollected statistics are sampled as well. Otherwise, the system recollects full statistics for the specified table.

  • You cannot collect sampled statistics on a COLUMN set that is also a component of the row partitioning expression of a partitioned table or join index; therefore, the system recollects full statistics on any such column.

    You can, however, collect sampled statistics on an INDEX set that contains partitioning columns.