COLLECT STATISTICS SQL Statement | Teradata Database - COLLECT STATISTICS (Optimizer Form) - Teradata Database

SQL Data Definition Language Syntax and Examples
Deployment
VantageCloud
VantageCore
Edition
VMware
Enterprise
IntelliFlex
Product
Teradata Database
Release Number
20.00
Publication Date
June 2025
Content Type
SQL Reference
Publication ID
B035-1144-200K
ft:locale
en-US
ft:lastEdition
2026-09-23
dita:mapPath
mbm1747160743883.ditamap
dita:ditavalPath
jlp1749501702948.ditaval
ft:sourceId
dita
dita:id
mdr1472255012272
lifecycle
latest

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 be actual or virtual. For definitions of actual and virtual, see PARTITION BY.

The columns can reside in any of the following:
  • 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 JSON Data Type, B035-1150.

For information about collecting statistics on ST_GEOMETRY columns, see Geospatial Data Types, B035-1181.

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.

ANSI Compliance

This statement is a Teradata extension to the ANSI SQL:2011 standard.

Although SET SESSION ACCOUNT is technically a DCL statement, the system treats it as a DDL statement for transaction semantics.

Other SQL dialects support similar non-ANSI standard statements with names such as the following:
  • CREATE STATISTICS
  • UPDATE STATISTICS

Required Privileges

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

Object on which to collect statistics Required privilege Object
Permanent or base global temporary table STATISTICS Table.
Materialized global temporary table none. None.
Volatile table none. None.
Join index STATISTICS Join index or its containing database or user.
Hash index STATISTICS Hash index or its containing database or user.
Table protected by a row-level security policy OVERRIDE SELECT CONSTRAINT Table.
Archived database object
  • STATISTICS
or
  • RESTORE
 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.

Database Object from which to Copy Statistics Object on which SELECT Privilege is Required
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.