The Teradata Collect Statistics Using Sample feature explained
Utilizing statistical samples in a Teradata system can lessen the workload and produce execution plans equivalent to those resulting from complete statistics when implemented with care.
Sample statistics estimate demographics from a subset of rows. Use documented COLLECT STATISTICS sampling options; defaults and saved options depend on release and configuration, rather than a universal 2% sample.
COLLECT STATISTICS USING SAMPLE 5 PERCENT COLUMN (YourColumn) ON YourDatabase.YourTable;
When Sampling Fits, and When It Does Not
Sampling reduces collection cost by reading fewer rows. Consider it when full collection is expensive and sampled estimates are adequate for representative predicates. Small tables gain little from sampling, while skew and rare values raise the risk of a misleading sample. A high number of distinct values is one consideration, not a universal eligibility rule. Use full statistics when a sample does not represent important values adequately, but remember that full statistics do not guarantee the best plan either: validate estimates and resource use.
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
When can sample statistics be applied effectively? Ensuring the table is not skewed is crucial in obtaining accurate sample statistics. To determine skewness, execute the following query:
SELECT HASHAMP(HASHBUCKET(HASHROW(YourPIColumn))), COUNT(*)
FROM YourDatabase.YourTable
GROUP BY 1;
Substitute the complete primary-index column set and account for the table map when interpreting AMP placement. HASHROW() with no arguments hashes a constant and does not measure PI distribution. There is no universal 5% skew cutoff: compare sampled estimates with actual predicate counts, and consider full statistics for small tables or distributions poorly represented by a sample.
COLLECT STATISTICS USING SAMPLE ON <TABLE> COLUMN (<COLUMN>);
System-Determined or Explicit Sample Size
To let Teradata choose the sample size:
COLLECT STATISTICS USING SYSTEM SAMPLE COLUMN (CustomerId) ON Customer;
An explicit percentage uses USING SAMPLE n PERCENT, as in the example above; USING SYSTEM 5 PERCENT is not valid syntax. Automatic sample-size behavior depends on the release and on saved options, so review the documented policy before relying on it.
Do you have to repeat the sampling option when recollecting?
Recollection can reuse the sampling options saved with the statistic, but what happens when an option is omitted depends on the release and the statistic definition. Inspect the existing options rather than assuming one policy applies to every recollection.
Statistics can be collected on volatile tables, join indexes and materialized global temporary tables. Full collection on these objects succeeded in the audit; sampling eligibility and syntax should be checked for the object type and release.
For more information on Teradata Statistics, please refer to Teradata Statistics: Everything You Need to Know.
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