DWHPro Insights

Evaluating the Accuracy of Table Statistics: Why Collection Date May Not Be Enough

By Roland Wenzlofsky ·

Evaluating the Accuracy of Table Statistics: Why Collection Date May Not Be Enough

Start with the tested SQL: The practical Teradata statistics guide includes COLLECT STATISTICS syntax, before-and-after EXPLAIN estimates, sampling and recollection examples tested on Teradata 20.00.28.81. The specialist examples below retain their original release context and were not retested in that lab.

When assessing the accuracy of statistics in a table, individuals often rely solely on the collection date. However, this may not always provide enough information. For example, static lookup tables may retain accurate Teradata statistics even if they have not been recently updated. Conversely, some tables change frequently, with certain fields exhibiting broader value ranges while others remain unchanged with a limited number of codes.

To validate your table’s statistics, use a WHERE condition to execute a simple query on the desired column. For instance:

SELECT * FROM YourDatabase.YourTable WHERE TEST_COLUMN = 'X';

Replace the example identifiers. Compare EXPLAIN estimated matching rows with the actual predicate row count. A percentage of total table size is not a reliable refresh threshold: changing 10 matching rows to 20 in a 1,000-row table is only 1% of the table but doubles predicate cardinality. Consider distribution changes and the workload when deciding whether to recollect.

Remember that the optimizer’s estimates may not always be exact to the row level. Nonetheless, the disparity between the expected and actual record count should not be substantial.

Need help with query performance or platform costs?

Diagnose plans, skew, spool pressure and expensive workloads. Agree a baseline and acceptance criteria before making changes.

Explore Teradata performance tuning

Practical data engineering insights, regularly

Join more than 3,500 data professionals: a regular newsletter with practical insights on data engineering, new data platforms (Snowflake, Databricks, Teradata) and AI.

Follow DWHPro in Google to see our articles more often in Search.

2 thoughts on “Evaluating the Accuracy of Table Statistics: Why Collection Date May Not Be Enough”

  1. Hi Alan,

    One example comes to my mind:

    In case the Teradata system configuration changes (for example, by adding nodes) and rows of a PPI Table are distributed to new partitions.

    This happens for example if you are using the HASHING functions of Teradata in the PARTITIONING definition of your table DDL:

    HASHAMP(HASHBUCKET(HASHROW(column)))

    As a bigger system has more AMPS, rows are distributed differently to the partitions and the statistics become stale and have to be recollected.

    This is an example, where the table content stayed the same but statistics became stale.

    Reply
  2. I was once advised to recollect statistics on all the static ‘lookup’ tables held in a database, on a monthly basis, ‘just in case’ the statistics needed to be refreshed…needless to say, I never saw a reason to do this as it never had any impact. Would there ever be a case where statistics become ‘stale’ and therefore ignored by the optimizer in such circumstances?

    Reply

Leave a Comment

Get more practical data engineering advice

Join the DWHPro newsletter for practical data engineering tips, troubleshooting examples and data platform insights.

Protected by Security by CleanTalk and CleanTalk Anti-Spam