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

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

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

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.

ANSI Compliance

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

Although SET SESSION ACCOUNT is technically a DCL statement, Vantage 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.