Statistics

Teradata optimizer comes up with an execution strategy for each SQL query. This execution strategy is based on the statistics collected on the tables used within the SQL query. Statistics on the table is collected using COLLECT STATISTICS command. Optimizer requires environment information and data demographics to come up with optimal execution strategy. The Purpose of COLLECT STATISTICS is to gather and store demographic data for one or more columns or indices of a table or join index.In this process it collects data and stores the summary in the Data Dictionary (DD) inside USER DBC.
The optimizer uses this synopsis data to generate efficient table access and join plans.

Environment Information

Number of Nodes, AMPs and CPUs

Amount of memory

Data Demographics

The number of rows in the table

The average row size

The range of values for the column(s) in which statistics were collected

The number of rows per value for the column(s) in which statistics were collected

The number of NULLs for the column(s) in which statistics were collected