Performance tuning using Collect Statistics in Teradata table with examples

Collect Statistics in Teradata

Collect statistics statement is collects the demographic data for one or more columns ,hash indexes , join indexes of the table and stores the synopsis in the data dictionary. The Optimizer uses the synopsis data to generate the table access and join plans.

The statistics collected by Collect statistics

Data demographics include the below details

  • Number of rows in the table
  • Row size
  • Number of unique values in the table
  • Range values in a table
  • Number of NULL’s for the columns
  • Information of all indexes

Syntax for the Collect Statistics in Teradata

#1.Collect statistics for column

#2.Collect statistics for Index

Example for collect statistics

Lets do the collect statistics for the customer table and it has the below records.

Example for collect statistics in Teradata

Now we are going to run the collect statistics statement for the columns Customer_Id and Region_code on the Customer table.

View statistics of a table

Help stats statement is used to view the collected statistics of a table in Teradata.

Syntax of Help Stats statement

Example for the Help stats

Example for Help stats in Teradata

To view the Optimizer suggestions in Teradata

Once we set the DIAGNOSTIC HELPSTATS, the optimizer will show the explain plan of the query which we are going to run in the Teradata. We can use this statement to check whether the stats are working well for a query.


Lets view the query plan using the Explain statement for the select query as below. It will give the stats collection recommendation that helps to improve the query performance.

Output of the Explain statement

Recommended Articles