Teradata statistics describe the values and distribution of data so the optimizer can estimate row counts and choose access and join plans. Use COLLECT STATISTICS to collect them, HELP STATISTICS to inspect the available statistics, and SHOW STATISTICS VALUES when you need histogram detail. Start with columns that matter to your workload rather than collecting every possible combination.
Updated and lab-tested 24 October 2026 on Teradata Database 20.00.28.81 with BTEQ 20.00.00.11, BTET transaction mode. The examples below verify syntax, result counts and execution-plan estimates; they are not a runtime benchmark.
COLLECT STATISTICS: quick reference
After creating the sample table in the next section, these statements collect a full statistic, list existing statistics and display the collected values:
COLLECT STATISTICS USING NO SAMPLE AND NO THRESHOLD
COLUMN (status_code) ON dwhpro_stats_demo;
HELP STATISTICS dwhpro_stats_demo;
SHOW STATISTICS VALUES COLUMN (status_code) ON dwhpro_stats_demo;
USING NO SAMPLE requests a full collection. NO THRESHOLD makes recollection explicit in this small lab. These are reproducibility choices, not a recommendation to scan every production table on every run. Sampling and threshold defaults can depend on the release, database configuration and previously saved collection options.
Tested example: compare estimated and actual rows
Run the following in a disposable lab database with the object name unused. You need permission to create and populate the table and collect its statistics, plus access to SYS_CALENDAR.CALENDAR. Submit statements separately, as in the test. The table contains 20,000 rows: 19,800 with status A and 200 with status B.
CREATE MULTISET TABLE dwhpro_stats_demo
(
row_id INTEGER NOT NULL,
status_code CHAR(1) NOT NULL,
region_code CHAR(1) NOT NULL
) UNIQUE PRIMARY INDEX (row_id);
INSERT INTO dwhpro_stats_demo
SELECT n,
CASE WHEN n <= 19800 THEN 'A' ELSE 'B' END,
CASE WHEN n <= 19800 THEN 'N' ELSE 'S' END
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY calendar_date) AS n
FROM SYS_CALENDAR.CALENDAR
QUALIFY n <= 20000
) AS src;
First collect only the primary-index column statistic. That gives the optimizer table-cardinality information without describing the distribution of status_code. Then compare the real counts with EXPLAIN:
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-- Establish table cardinality without collecting the filter-column histogram.
COLLECT STATISTICS USING NO SAMPLE AND NO THRESHOLD COLUMN (row_id) ON dwhpro_stats_demo;
SELECT status_code, COUNT(*) AS actual_rows
FROM dwhpro_stats_demo GROUP BY 1 ORDER BY 1;
-- BEFORE filter-column statistics.
EXPLAIN SELECT row_id FROM dwhpro_stats_demo WHERE status_code = 'B';
The actual count for B was 200. The filter step estimated 2,000 rows with no confidence. Now collect the statistic on the filtered column and explain the same query again:
COLLECT STATISTICS USING NO SAMPLE AND NO THRESHOLD COLUMN (status_code) ON dwhpro_stats_demo;
-- AFTER filter-column statistics: compare the estimate with the actual 200 rows.
EXPLAIN SELECT row_id FROM dwhpro_stats_demo WHERE status_code = 'B';
The estimate became 200 rows with high confidence. Both plans still used an all-AMP scan. This demonstrates a better row estimate, not a measured reduction in elapsed time. A different release, data distribution or optimizer configuration can produce different estimates.
Compare the estimate at the relevant plan step with actual rows. A confidence label tells you about the basis for an estimate; it does not guarantee that the estimate is correct or that the overall plan is fast. For deeper examples, continue with statistics and cardinality estimation case studies.
Which columns need statistics?
- Join columns: especially when bad estimates can change join order, redistribution or duplication decisions.
- Filter columns: particularly skewed values and predicates that reduce a large table to a small result.
- Primary-index and partition information: consider the table design and the estimates needed by important queries. A primary-index statistic alone did not describe our non-indexed status column.
- Correlated column groups: consider a multicolumn statistic when predicates or joins depend on the relationship between values.
Use workload evidence to prioritize. Collection consumes resources, and a statistic that changes no relevant decision may not justify frequent recollection. DIAGNOSTIC HELPSTATS can reveal optimizer suggestions; validate their benefit rather than treating every suggestion as an instruction. See how to evaluate a statistics collection.
Single-column versus multicolumn statistics
COLUMN (status_code, region_code) describes a joint distribution. It is not shorthand for two independent column statistics. Our sample deliberately correlates A with N and B with S:
-- A separate single-column statistic and a joint statistic have different scopes.
COLLECT STATISTICS USING NO SAMPLE AND NO THRESHOLD COLUMN (region_code) ON dwhpro_stats_demo;
COLLECT STATISTICS USING NO SAMPLE AND NO THRESHOLD COLUMN (status_code, region_code) ON dwhpro_stats_demo;
HELP STATISTICS dwhpro_stats_demo;
SHOW STATISTICS VALUES COLUMN (status_code) ON dwhpro_stats_demo;
The lab verified collection and inspection of all four statistics: row ID, status, region, and the status/region pair. It did not demonstrate a separate performance gain from the pair. Choose the grouping because a query needs that relationship, not because more columns must be better. Read the multicolumn statistics explanation for the specialist discussion.
When should you use sample statistics?
Sampling can reduce collection work on large tables. It can also give poor information about rare values or uneven distributions. Compare the resulting estimates and important plans with a full-collection baseline before adopting a percentage across a workload.
-- Exercise explicit sampling, then return this example to a full collection.
COLLECT STATISTICS USING SAMPLE 20 PERCENT AND NO THRESHOLD
COLUMN (status_code) ON dwhpro_stats_demo;
COLLECT STATISTICS USING NO SAMPLE AND NO THRESHOLD
COLUMN (status_code) ON dwhpro_stats_demo;
These commands exercise an explicit 20% sample and then return this statistic to a full collection. The percentage is a syntax example, not a recommended default. For operational choices and release-specific behavior, see when to use Teradata sample statistics.
When should statistics be recollected?
Recollect when the relevant data distribution has changed enough to make estimates unreliable. Collection age and table growth are useful signals, but neither describes every change. A status transition can change selectivity without adding a single row.
-- Change values without changing the table's row count.
UPDATE dwhpro_stats_demo
SET status_code = 'B', region_code = 'S'
WHERE row_id BETWEEN 19601 AND 19800;
SELECT status_code, COUNT(*) AS actual_rows
FROM dwhpro_stats_demo GROUP BY 1 ORDER BY 1;
The table still contained 20,000 rows, but B now matched 400. After recollecting the affected statistics, EXPLAIN estimated 400 rows with high confidence in this lab. This is why a row-count-only refresh policy can miss meaningful changes. The full script includes the recollection statements and cleanup.
Production schedules should account for load completion, distribution changes, important query plans and collection cost. Use automation and thresholds where appropriate, and verify that a submitted collection actually refreshed the statistic. Read why the collection date alone cannot prove statistics are accurate.
What if a query gets slower after collecting statistics?
Updated information can change the chosen plan. That can expose a missing correlated statistic, a different join order or another limitation; an optimizer defect is also possible. Do not assume that either the new statistics or the application must be at fault.
- Capture the before/after SQL, plan, collection options and data state.
- Find the first substantial estimate or access-path change. Check actual rows where logging provides them.
- Check skew, correlations, predicates and statistics on the other participating tables.
- Compare CPU, I/O, spool, elapsed time and concurrent load under comparable conditions.
- Use the evidence to choose a targeted correction or a supported escalation. Avoid dropping all statistics as a blanket fix.
The DBQL guide explains what logged query evidence can and cannot tell you. If the issue is an access path rather than an estimate, the tested join-index examples show another tuning option and its maintenance trade-offs.
Common statistics mistakes
- “A recent collection means accurate estimates.” Check the data distribution, collection scope and whether thresholds skipped a refresh.
- “Statistics always make a query faster.” They inform planning. A plan may stay the same, improve or regress.
- “High confidence means the result count is correct.” Compare with actual rows; stale information can still underpin a confident estimate.
- “Collecting on the primary index covers every filter.” The tested status-column example shows why it does not.
- “One sample percentage is right for every table.” Validate rare values, skew and the needs of the workload.
Download and reproduce the SQL
Download the tested statistics and join-index SQL bundle. It includes setup, inspection, cleanup and expected results. Use a separate lab database: the scripts create, change and drop only their named example objects. The bundle contains no credentials or database-administration setup script.
The syntax and options are documented in Teradata’s COLLECT STATISTICS reference. Check the documentation for your installed release and choose production collection settings deliberately.
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 Roland,
I always try collect stats at least on PIs of tables & on columns which are being frequntly used in joins/WHERE.
Is that fine as per the above disscussion .
Thanks,
Nagendra
Collecting full statistics is the best method in order to achieve the best possible execution plan.
We can’t always do it as the collecting of statistics is costly (Disk IOs, CPU time), but if
the costs are not a problem (for example, if you have a batch window for this task),
collecting statistics on all primary indexes and all WHERE conditions definitely is a good decision.
Having collected statistics for the WHERE condition is very important; If these statistics are missing, the Optimizer will use simple heuristics to determine the spool cardinality.
This is how the heuristic works:
The first WHERE conditions are always estimated to be 10% of the total table rows.
If, for example, our table contains 100,000 rows, the Optimizer assumes that 10,000 rows will be retrieved.
Be aware, the 10% rule is applied to the estimated table cardinality which is either determined by random-AMP sampling or available form collected primary index statistics. Skewed tables probably will lead to a heuristic, which is based on a wrong table cardinality estimation.
Therefore, statistics on WHERE conditions and primary index statistics work together!
Here are some examples:
— No statistic on the WHERE conditions available
SELECT * FROM The_Table WHERE col=10; — 10,000 rows are estimated, even if not a single row has a column value = 10 !
For each further ANDed column, 75% of the previous estimation are taken:
SELECT * FROM The_Table WHERE col=10 AND col2 = 20; — 10,000 * 75% = 7,500 rows are estimated
In case of ORed conditions, each column selected, accounts for 10%:
SELECT * FROM The_Table WHERE col=10 OR col2 = 20; — 10,000 + 10,000 = 20,000 rows are estimated
In case we have statistics on one or more of the WHERE condition columns, the one with the highest selectivity (which estimate the least rows to be returned) is applied:
COLLECT STATISTICS ON THE_TABLE COLUMN(col); — We assume the estimated cardinality is 5,000 rows
COLLECT STATISTICS ON THE_TABLE COLUMN(col2); — We assume the estimated cardinality is 10,000 rows
SELECT * FROM The_Table WHERE col=10 AND col2 = 20; — 5,000 * 75% = 3,750 rows are estimated
The statistics of column col (5,000 estimated rows) are used as a base for applying heuristics on col2.
The collected statistics of col2 are completely ignored!
The heuristics for columns, which are having collected statistics, are not always using the 75% rule.
If the value is skewed (being available more than 75% of the time), the real number is used.
For example, if the column col2 would contain the value = 20 in 90% of the rows, 90% would be used in the heuristic
calculation: 5,000 * 90% = 4,500
Another important fact is that as soon as any heuristics are included in the estimation,
the confidence level of the retrieving step is lowered to “no confidence”.
This will have an impact on the execution plan.
If you want to achieve high confidence, the Optimizer requires multi-column statistics:
COLLECT STATISTICS ON THE_TABLE COLUMN(col,col2);
Conclusion:
The statistics you mention absolutely make sense, but I would add as well statistic on the join column(s) of each join step, as this helps the Optimizer in join planning (order of join steps, join type, redistribution strategy, etc.)
I hope this helped
Roland
Hello Tom!
Thank you very much for the encouraging comment!
We are excited to receive such positive feedback from a master of the trade!
I suggest that we continue our discussion using my e-mail address:
[email protected]
Roland,
I really like the way you explain things. You hit important points without digressing into unimportant or confusing details. You write as I teach! I would like for you to consider co-authoring a book with me. You are that good!
Hi Roland,
I couldn’t agree more, there is so much misunderstanding in this area.
One of the most common things I hear is that “I collected statistics and it improved the confidence level. That is good isn’t it?”
The number of times I’ve had to explain to them that if the ** only ** change is confidence then the stats are a waste of time, they will not have improved performance. The following discussion is then about ‘structure of the explain plan’ with my finishing line being that “for statistics to be effective they MUST change the structure of the explain plan – otherwise they are a complete waste of resources”.
Cheers,
Dave
Confidence levels are a very good point, thanks, David.
Statistics are a topic which is close to my heart as in my opinion each performance optimization should start with a review of statistics (are statistics missing? are they stale?) but in my experience, this task is many times skipped for different reasons: Sometimes a lack of understanding, but sometimes the environment of the Data Warehouse does not allow to “experiment” with statistics.
I will give you an example: In one of my last projects, it was not possible to do proper performance optimization as only database administrators were allowed to add/remove statistics. Each required change needed at least one day. This actually forced me to skip statistics considerations completely and I had to rely on query rewriting only.
Another mistake I can see quite often is that people look only at the date of the last recollection and decide based on this date if statistics are stale.