Teradata optimizer gives an execution strategy for every SQL query. This execution strategy is based on the statistics collected on the tables used within the SQL query. Statistics on the table are collected using COLLECT STATISTICS command.
The COLLECT STATISTICS (Optimizer form) statement collects demographic data for one or more columns of a base table, hash index, or join index, computes a statistical profile of the collected data, and stores the synopsis in the Data Dictionary.
The Optimizer uses the synopsis data when it generates its table access and joins plans.
Teradata Statistics environment needs the following:
Data Demographics consider the following:
We should collect statistics on newly created, empty data tables. An empty collection defines the columns, indexes, and synoptic data structure for loaded groups.
We can easily collect statistics again after the table is populated for prototyping, and back when it is in production.
We can collect statistics in the following ways.
How to approach Collect Statistics
There are three approaches to collect statistics on the table.
Collecting Statistics on Table
COLLECT STATISTICS command is used to collect statistics on a table.
Following is the basic syntax to collect statistics on a table.
Consider an Employee table with the following records, such as:
We are going to run the following query to collect statistics for the Emp_Id, First_Name columns of the Employee table.
When the above query is executed, it produces the following output.
Update completed. 2 rows changed.
We can view the collected statistics using the HELP STATISTICS command.
Following is the syntax to view the statistics collected.
Following is an example to view the statistics collected on the Employee table.
When the above query is executed, it produces the following table with updated columns and their values.
Date Time Unique Values Column Names 6/2/20 10:05:02 5 * 6/2/20 10:05:02 5 Emp_Id, First_Name