Teradata Statistics Collection for Improved Query Performance

 

Teradata Collect Statistics – The Decision

Assuming that all Teradata physical data model and table design issues have been resolved, it is expected that your tables can be regularly utilized.

Inform Teradata of a table’s expected properties before executing an SQL statement, with a clear directive to utilize the information. Teradata can more accurately estimate the path through a query by doing so.

Statistics are a potent and costless tool for accomplishing this.

 

The Scope of the Teradata Statistics Collection

Statistics can be collected on a single column or a combination of columns.

The table’s physical design influences what you gather; otherwise, it should be based on how it is utilized.

Always include the following as initial collection choices:

• on the dummy column PARTITION, irrespective of whether your table is partitioned or not
• on the Primary Index columns (in combination)
• on the Secondary Index columns (in combination)
• on the Columns used for partitioning

The second usage-driven set of collect options consists of:

• Join columns: columns typically used to join the table to others.
• WHERE condition columns: columns frequently used to select data subsets from the table

Based on our experience, certain attributes should be excluded from the collection unless there is compelling evidence to include them.

• time and date columns in table landscapes of daily changes
• Code, type, or flag columns hardly used in follow-up queries