Missing statistics can affect estimates for both indexed and nonindexed columns. Dynamic sampling and heuristics may help, but neither the presence nor absence of an index guarantees a good plan.
Part of the Teradata Statistics and Cardinality Estimation guide.
This article explores Optimizer’s ineffective method for estimating the cardinality of non-indexed columns.
Teradata Heuristics – A Nasty Surprise
In the absence of statistics on non-indexed WHERE condition columns, the Optimizer can resort to a basic estimation method based on the row count:
The percentage heuristics in this historical example are not universal optimizer rules. On a 10,000-row fixture, actual cardinality was 180: estimates were 748 without column statistics, 6,750 with only first-column statistics, and 180 with both single-column statistics or joint statistics. Joint statistics improved confidence; they do not eliminate all heuristics.
The collected or random-AMP statistics for the primary index determine the table’s cardinality.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free bookHeuristics can be unexpectedly problematic.
A brief comment is necessary regarding the estimation of columns joined with OR.
Teradata 14.10 implements the aforementioned rule. In prior releases of Teradata, the reduction of the above estimates is at a rate of 1% for each intersection in set theory, including:
Altogether: Heuristics are a bad way to estimate the cardinality of retrieve steps.
The Optimizer’s Toolkit to Improve Estimations
Fortunately, the Optimizer can utilize supplementary information to enhance estimations for non-indexed WHERE condition columns.
COLLECT STATISTICS ON The_Table COLUMN first_column;
Based on the statistics in first_column, we assume that there will be 5,000 rows.
SELECT * FROM The_Table
WHERE
first_column = 'A' -- 5,000
AND second_column = 'B' -- 75% of above estimation
The example illustrates how the Optimizer utilizes statistics to enhance the initial estimation point for subsequent calculations.
What occurs when we gather statistics for both the first_column and second_column?
COLLECT STATISTICS ON The_Table COLUMN first_column; - 5,000 rows with value 'A'
COLLECT STATISTICS ON The_Table COLUMN second_column; - 200 rows with value 'B'
SELECT * FROM The_Table
WHERE
first_column = 'A' -- 75% of below estimation
AND second_column = 'B' -- 200 rows
The Twisted World of the Optimizer: How Skew Improves the Estimations
The Optimizer utilizes the skew information regarding the first_column column to improve the estimation accuracy.
As previously mentioned, we assume that 90% of rows in the first column have the value ‘A’, resulting in a skewed distribution. To analyze this bias, we can utilize histograms designed for such values.
COLLECT STATISTICS ON The_Table COLUMN first_column; - 5,000 rows with value 'A'
COLLECT STATISTICS ON The_Table COLUMN second_column; - 200 rows with value 'B'
SELECT * FROM The_Table
WHERE
first_column = 'A' -- 90% of below estimation
AND second_column = 'B' -- 200 rows
Estimated number of rows: 180.
Utilizing biased value information enhances estimations.
To improve estimations beyond utilizing only one WHERE condition column, consider exploring alternative methods for optimization.
Multi-column statistics provide the solution.
COLLECT STATISTICS ON The_Table COLUMN (first_column,second_column);
SELECT * FROM The_Table
WHERE
first_column = 'A'
AND second_column = 'B';
Another reason to avoid heuristics is their potential drawbacks.
Suppose heuristics are used to estimate the cardinality of at least one column. In that case, the retrieval process will result in “no confidence” and limit the potential optimizations available to the Optimizer, creating a conservative execution plan.
I hope you enjoyed the content, and do not hesitate to ask for clarification or additional information.
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.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free book

Hi Akilan, especially for you. My new article covering this topic:
https://www.dwhpro.com/teradata-multi-column-statistics/
Hi –
It’s good stuff. Kindly clarify the below one,
Multi-column stats are defined and a few columns are part of the SQL. How the estimation will happen there?
COLLECT STATISTICS ON The_Table COLUMN (first_column,second_column);
SELECT * FROM The_Table
WHERE
first_column = ‘A’;
Thanks
This is an awesome explanation.
Very good explanation, thank you!