15.10 - COLLECT STATISTICS (Optimizer Form) - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K

Purpose

Collects demographic data for one or more columns, computes a statistical profile of the collected data, stores the synopsis in DBC.StatsTbl in the data dictionary, and optionally copies the statistics for one or more columns to a duplicate target table.

The columns can reside in a:
  • table
  • hash index
  • join index

The Optimizer uses the data when generating access and join plans.

You cannot collect statistics on columns with the following data types.
  • BLOB
  • CLOB
  • JSON
  • XML
  • Nondeterministic UDT
  • BLOB-derived UDT
  • CLOB-derived UDT
  • XML-derived UDT
  • ARRAY/VARRAY
  • Period
  • Derived Period

Although you cannot collect statistics on an entire column with the JSON data type, you can collect statistics on extracted portions of the JSON document. See Teradata JSON, B035-1150.

This statement collects statistical and demographic information only for use by the Optimizer. For information about collecting statistics for a query capture database, see “COLLECT STATISTICS (QCD Form)” in SQL Data Manipulation Language, B035-1146.

Privileges

The following table lists the privileges required to collect statistics on various database objects.

Object on which to collect statistics Required privilege Object
a permanent or base global temporary table STATISTICS the table.
a materialized global temporary table none. none.
a volatile table none. none.
a join index STATISTICS the join index or its containing database or user.
a hash index STATISTICS the hash index or its containing database or user.
a table protected by a row-level security policy OVERRIDE SELECT CONSTRAINT the table.
an archived database object
  • STATISTICS

or

  • RESTORE
the archived object.

This enables you to restore archived statistics from an archived data source.

The following table lists the privileges required to copy statistics using the COLLECT STATISTICS … FROM source_table syntax.

To copy statistics from this database object … You must have the SELECT privilege on the …
base table base table or its containing database or user.
join index underlying table set of the join index or its containing database or user.
hash index underlying table of the hash index or its containing database or user.

The following table lists the privileges required to submit a COLLECT STATISTICS request on various database objects that are protected by row-level security constraints.

To execute COLLECT STATISTICS with a VALUES clause on this row-level security-protected database object … You must have the STATISTICS privilege plus the OVERRIDE SELECT CONSTRAINT privileges on the …
base table table or its containing database or user.
join index underlying table set of the index or its containing database or user.
hash index underlying table of the index or its containing database or user.

Syntax