COLLECT STATISTICS SQL Statement | VantageCloud Lake - COLLECT STATISTICS (Optimizer Form) - Teradata Vantage

Teradata® VantageCloud Lake
Deployment
VantageCloud
Edition
Lake
Product
Teradata Vantage
Published
January 2023
ft:locale
en-US
ft:lastEdition
2024-12-11
dita:mapPath
phg1621910019905.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
phg1621910019905

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 COLUMN in CREATE FOREIGN TABLE Syntax Elements.

The columns can reside in any of the following:
  • Table
  • 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
  • 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 Collecting Statistics on JSON Data.

For information about collecting statistics on ST_GEOMETRY columns, see Teradata Vantage™ - Geospatial Data Types, B035-1181. For information on optimizer statistics, see Statistics Enhancements Orange Book, 541-0010042.

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).

Also see the following sections:

ANSI Compliance

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

Vantage treats SET SESSION ACCOUNT (a DCL statement) 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 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.
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.

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

Database Object Protected by Row-Level Security Constraint Items on Which You Need STATISTICS and OVERRIDE SELECT CONSTRAINT Privileges
Base table Table or its containing database or user.
Join index Underlying table set of index or its containing database or user.