Whenever the user executes any query, Teradata creates different execution plans and chooses the best plan to execute the SQL query. These execution plans are based on the statistics collected on the tables used within the SQL query.
The information collected by the
COLLECT STATISTIC method stores in the Data Dictionary(DD) tables. This information is used by the Teradata optimizer during the creation of query execution plans and joins plans.
Followings are the information Teradata Collects using collect statistics methods.
- The number of rows in the table.
- The average row size.
- The range of values in the column(s) where statistics are collected.
- Information about indexes in the table.
- The number of rows per value for the column where statistics are collected.
- The number of Nodes, AMPs, CPUs.
- Amount of Memory.
Teradata Collect Statistics Syntax
The syntax to collect statistics as follows.
COLLECT [SUMMARY] STATISTICS INDEX(index_name) COLUMN(column_name) ON <table_name>;
Teradata Collect Statistics Example
The following example collects statistics on the employee table.
COLLECT STATISTICS COLUMN(empid) on Teradatapoint.employee;
In Teradata you can check about the statistical information like on which column(s) and when statistics have been collected. You can view the statistics information using the HELP STATISTICS command.
The following syntax shows the statistics collected on the table.
HELP STATISTICS <table_name>;
In the following example, we check the statistics information in the employee table.
HELP STATISTICS Teradatapoint.employee;
The result of the above query is as below.
Teradata stores all the collect statistics information in the DBC in the dictionary tables. Those are –
- DBC.Indexes (for multicolumn index only)
- DBC.TVFields (for all columns and single column indexes)
- DBC.statsTbl (Teradata v14 and beyond)