Teradata StatisticsTeradata 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. Environment InformationTeradata Statistics environment needs the following:
Data DemographicsData Demographics consider the following:
UsageWe 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 StatisticsThere are three approaches to collect statistics on the table.
Collecting Statistics on TableCOLLECT STATISTICS command is used to collect statistics on a table. Syntax Following is the basic syntax to collect statistics on a table. Example 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. Viewing StatisticsWe can view the collected statistics using the HELP STATISTICS command. Syntax Following is the syntax to view the statistics collected. Example 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 Next TopicTeradata Compression |