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 select the 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 are 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