COLLECT STATISTICS IN TERADATA

COLLECT STATISTICS IN TERADATA: This post will help you to understand collect stats importance in teradata database.DK®

The Parsing Engine (Optimizer )in Teradata uses the statistics collected on a table to determine the best plan for the AMPs to follow to execute a query. DK®

The PE uses Collect Statistics to decide between using an index or if it should perform a Full Table Scan. The PE also needs to know if a table is large or small, if it has a bunch of NULL values, and how many duplicate values exist per average.DK®

We don't collect stats on every column.DK®

The statistics are usually stored inside DBC tables(Only DBA can access) and it consumes the perm space.DK®

We only collect on certain columns and indexes such as the below :
  1)  All Non-Unique Indexes
  2)  Columns frequently used in user queries in the WHERE Clause
  3)  All Primary Indexes of small tables
  4)  Columns used as Join Conditions

SYNTAX :DK®

Collect statistics on COLUMN_NAME;
Eg: We have table Employee with Column Employee_Id,Employee_Name
collect statistics on Employee column Employee_Id;
collect statistics on Employee column (Employee_Id,Employee_Name);

Collect statistics in INDEX_NAME;
Eg: We have table Employee with Index Emp_Num
collect statistics on Employee Index Emp_Num;