15.00 - Collecting Statistics When No COLUMN or INDEX Clause Is Specified - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Teradata Database
Programming Reference

Collecting Statistics When No COLUMN or INDEX Clause Is Specified

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.