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

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
wgr1555383704548.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

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 Vantage™ - JSON Data Type, B035-1150.

For information about collecting statistics on ST_GEOMETRY columns, see Teradata Vantage™ - 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 Teradata Vantage™ - 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
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.