DWHPro Insights

Optimizing Workload with Teradata’s Sample Statistics Feature

By Roland Wenzlofsky ·

Optimizing Workload with Teradata’s Sample Statistics Feature

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.

Teradata Query Performance Tuning: The DWHPro Guide book cover

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
Skewed value distribution that a row sample can misrepresent

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.

Explore Teradata performance tuning

Teradata Query Performance Tuning: The DWHPro Guide book cover

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

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

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

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

Protected by Security by CleanTalk and CleanTalk Anti-Spam