Evaluating the Accuracy of Table Statistics: Why Collection Date May Not Be Enough
Learn how to accurately evaluate the statistics of a table beyond just the collection date. Find out how to test the validity of your table's statistics with a simple query.
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 TABLE WHERE TEST_COLUMN = 'X';Compare the estimated record count, visible in Teradata Administrator, with the returned record count. If the discrepancy exceeds a single-digit percentage relative to the table's overall size, the statistics likely require updating.
Want more practical data engineering analysis like this?
Join DWHPro Letters and get field-tested notes on Teradata, Snowflake, AI, migrations, performance, and enterprise data work. DWHPro Letters is free. Subscribe to get new issues by email.
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.
Planning or surviving an enterprise data platform migration?
I write regularly about the performance, cost, architecture, and project mistakes that show up in real Teradata, Snowflake, Databricks, and enterprise data work.
Subscribe for free and keep launch access.
Written by Roland Wenzlofsky, founder of DWHPro and author of Teradata Query Performance Tuning. DWHPro has helped data warehouse practitioners for 15+ years.