DWHPro Insights

Teradata Statistics and Cardinality Estimation

By Roland Wenzlofsky ·

Start with the tested SQL: The practical Teradata statistics guide includes COLLECT STATISTICS syntax, before-and-after EXPLAIN estimates, sampling and recollection examples tested on Teradata 20.00.28.81. The specialist examples below retain their original release context and were not retested in that lab.

Learning path: Statistics and row estimates. View the suggested reading order. This reference includes release-specific examples; check the version context before applying a technique.

Incorrect row-count estimates can lead to poor Teradata query plans. Statistics are one important input to those estimates. This guide collects everything on this site about statistics and cardinality estimation — what the optimizer does with the statistics you give it, how it copes when they are missing or stale, and how to diagnose an estimate that has gone wrong.

Optimizing Statistics and Avoiding Heuristics

The Optimizer typically excels in utilizing statistics, yet examining the execution plan and cardinality estimations can sometimes be beneficial.

Since Teradata 14.10, I have habitually included the SHOW STATISTICS statement in my considerations. The resulting metrics can aid in identifying statistical issues.

Teradata Statistics – Avoid the Heuristics

This case study demonstrates how the Optimizer can be compelled to utilize precise statistics rather than falling back on imprecise heuristics.

The case study relies on two tables as its foundation:

SHOW TABLE TheDatabase.TableOneDate;
CREATE SET TABLE TheDatabase.TableOneDate
(
OneDate DATE FORMAT 'YYYY-MM-DD'
)  UNIQUE PRIMARY INDEX ( OneDate );
SELECT COUNT(*) FROM TheDatabase.TableOneDate;
--> 1 Row

SHOW TABLE TheDatabase. TheCalendar

CREATE MULTISET TABLE TheDatabase.TheCalendar
(
CalendarDate DATE FORMAT 'yyyy-mm-dd'
) 
PRIMARY INDEX ( CalendarDate );
SELECT COUNT(*) FROM TheDatabase.TheCalendar;

–> 36.889 Rows

I excluded irrelevant columns from both tables and gathered statistics on the remaining ones.

COLLECT STATS ON TheDatabase. TableOneDate COLUMN OneDate;
 COLLECT STATS ON TheDatabase. TheCalendar COLUMN CalendarDate;

This query, which is not identical but conveys the same meaning, has attracted my attention due to its use in numerous badly-performing reports:

Explain SELECT * FROM TheDatabase.TheCalendar
WHERE CalendarDate >=
(
SELECT
OneDate
FROM TheDatabase.TableOneDate
)
1) First, we lock a distinct TheDatabase."pseudo table"
for read on a RowHash to prevent global deadlock for
TheDatabase.TableOneDate.
2) Next, we lock a distinct TheDatabase."pseudo table" for
read on a RowHash to prevent global deadlock for
TheDatabase.TheCalendar.
3) We lock TheDatabase.TableOneDate for read, and we
lock TheDatabase.TheCalendar for read.
4) We do an all-AMPs RETRIEVE step from
TheDatabase.TableOneDate by way of an all-rows
scan with no residual conditions into Spool 1 (all_amps)
built locally on the AMPs. The size of Spool 1 is estimated with
high confidence to be 1 row (25 bytes). The estimated time for
this step is 0.03 seconds.
5) We do an all-AMPs DISPATCHER RETRIEVE step from Spool 1 (Last Use)
by way of an all-rows scan and send the rows back to the
Dispatcher. The size is estimated with high confidence to be 1
row. The estimated time for this step is 0.01 seconds.
6) We do an all-AMPs RETRIEVE step from
TheDatabase.TheCalendar by way of an all-rows scan with a
condition of ("TheDatabase.TheCalendar.CalendarDate >= :%SSQ20")
into Spool 2 (group_amps), which is built locally on the AMPs.
The size of Spool 2 is estimated with no confidence to be 12,297
rows (4,943,394 bytes). The estimated time for this step is 0.04
seconds.
7) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 2 are sent back to the user as the result of
statement 1. The total estimated time is 0.08 seconds.

Although the execution plan appears satisfactory, there is a concern regarding the estimated number of rows in step 6. The estimate of 12,297 is significantly lower than the actual result set of 32,482 rows.

In this historical plan, the scalar-subquery value was not available for a value-specific estimate during the initial planning step. Confidence and estimates are not actual counts. The EXISTS alternative below is equivalent only when the source is guaranteed to contain exactly one row; multiple scalar rows cause an error, while EXISTS has different semantics.

12,297 (estimation) / 36,889 (table cardinality) = 33%

This can be demonstrated easily with a comparable configuration: The Optimizer consistently approximates that the number of rows in the result set will be one-third of the total table rows.

The following is an abbreviated historical histogram dump, not executable COLLECT STATISTICS syntax. Ellipses and omitted values prevent replay. Use SHOW STATISTICS VALUES to inspect the complete statistics on your own table.

COLLECT STATISTICS
COLUMN ( CalendarDate )
ON TheDatabase.TheCalendar
VALUES
(
/** SummaryInfo **/
/* NumOfBiasedValues */ 0,
/* NumOfEHIntervals */ 249,
/* NumOfHistoryRecords */ 7,
/* MinVal */ DATE '2000-01-02',
/* MaxVal */ DATE '2100-12-31',
/* ModeVal */ DATE '2000-01-02',
/* HighModeFreq */ 1,
/* NumOfDistinctVals */ 36889,
/* NumOfRows */ 36889,
/** Interval: MaxVal, ModeVal, ModeFreq, LowFreq, OtherVals, OtherRows **/
/* 1 */ DATE '2000-10-04', DATE '2000-01-02', 1, 1, 276, 276,
..
/* 249 */ DATE '2100-12-31', DATE '2100-09-08', 1, 1, 114, 114,
Explain SELECT * FROM TheDatabase.TheCalendar
WHERE CalendarDate >=
(
SELECT
DATE'2012-01-26'
)
1) First, we lock a distinct TheDatabase."pseudo table"
for read on a RowHash to prevent global deadlock for
TheDatabase.TheCalendar.
2) Next, we lock TheDatabase.TheCalendar for read.
3) We do an all-AMPs RETRIEVE step from
TheDatabase.TheCalendar by way of an all-rows scan with a
condition of ("TheDatabase.TheCalendar.CalendarDate >= DATE
'2012-01-26'") into Spool 1 (group_amps), which is built locally
on the AMPs. The size of Spool 1 is estimated with high
confidence to be 32,482 rows (1,006,942 bytes). The estimated
time for this step is 0.03 seconds.
4) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 1 are sent back to the user as the result of
statement 1. The total estimated time is 0.03 seconds.

The updated execution plan reveals that the Optimizer effectively utilizes the gathered statistics to accurately project the number of result set rows. Regrettably, my report optimization task does not allow for using a literal.

To better understand the Optimizer’s design, I modified the query once more and chose the literal from a “dummy table.”

Explain SELECT * FROM TheDatabase.TheCalendar
WHERE CalendarDate >=
(
SELECT DATE'2012-01-26' FROM
(
SELECT 1 AS x
) x
)
1) First, we lock a distinct TheDatabase."pseudo table"
for read on a RowHash to prevent global deadlock for
TheDatabase.TheCalendar.
2) Next, we lock TheDatabase.TheCalendar for read.
3) We do an INSERT into Spool 2.
4) We do an all-AMPs RETRIEVE step from Spool 2 (Last Use) by way of
an all-rows scan into Spool 1 (used to materialize view, derived
table, table function or table operator x) (group_amps), which is
built locally on the AMPs. The size of Spool 1 is estimated with
high confidence to be 1 row (22 bytes). The estimated time for
this step is 0.01 seconds.
5) We do a group-AMP RETRIEVE step from Spool 1 (Last Use) by way of
an all-rows scan into Spool 3 (group_amps), which is built locally
on that AMP. The size of Spool 3 is estimated with high
confidence to be 1 row (25 bytes). The estimated time for this
step is 0.01 seconds.
6) We do a group-AMP DISPATCHER RETRIEVE step from Spool 3 (Last Use)
by way of an all-rows scan and send the rows back to the
Dispatcher. The size is estimated with high confidence to be 1
row. The estimated time for this step is 0.01 seconds.
7) We do an all-AMPs RETRIEVE step from
TheDatabase.TheCalendar by way of an all-rows scan with a
condition of ("TheDatabase.TheCalendar.CalendarDate >= :%SSQ20")
into Spool 4 (group_amps), which is built locally on the AMPs.
The size of Spool 4 is estimated with no confidence to be 12,297
rows (381,207 bytes). The estimated time for this step is 0.03
seconds.
8) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 4 are sent back to the user as the result of
statement 1.

The execution plan indicates a return to heuristics, which is not ideal. I intended to enhance all reports’ performance by optimizing the “calendar view section.”

After much consideration, I devised a correlated subquery for the following query:

Explain SELECT * FROM TheDatabase.TheCalendar t01
WHERE EXISTS
(
SELECT
*
FROM TheDatabase.TableOneDate t02
WHERE t01.CalendarDate>=OneDate
)
1) First, we lock a distinct TheDatabase."pseudo table"
for read on a RowHash to prevent global deadlock for
TheDatabase.t01.
2) Next, we lock a distinct TheDatabase."pseudo table" for
read on a RowHash to prevent global deadlock for
TheDatabase.t02.
3) We lock TheDatabase.t01 for read, and we lock
TheDatabase.t02 for read.
4) We do an all-AMPs SUM step to aggregate from
TheDatabase.t02 by way of an all-rows scan with no
residual conditions. Aggregate Intermediate Results are computed
globally, then placed in Spool 3.
5) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of
an all-rows scan into Spool 2 (all_amps), which is duplicated on
all AMPs. The size of Spool 2 is estimated with high confidence
to be 72 rows (1,224 bytes).
6) We do an all-AMPs JOIN step from TheDatabase.t01 by way
of an all-rows scan with no residual conditions, which is joined
to Spool 2 (Last Use) by way of an all-rows scan.
TheDatabase.t01 and Spool 2 are joined using an
inclusion product join, with a join condition of (
"TheDatabase.t01.CalendarDate >= OneDate"). The
result goes into Spool 1 (group_amps), which is built locally on
the AMPs. The size of Spool 1 is estimated with low confidence to be
32,482 rows (1,006,942 bytes). The estimated time for this
step is 3.77 seconds.
7) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 1 are sent back to the user as the result of
statement 1.

The execution plan has been revised, allowing the Optimizer to utilize the statistics effectively. As a result, the estimations precisely match the number of rows in the result set.

The product join in the updated execution plan is insignificant due to the limited amount of data. However, what holds greater significance is the improved estimations. Since the “calendar view part” is frequently cross-joined in various reports, precise estimates are imperative.

I hope this case study encourages you to delve further into the intricacies of Optimizer. As previously noted, the “SHOW STATISTICS” command offers a plethora of valuable information.

The optimizer can also be asked directly: DIAGNOSTIC HELPSTATS reports which statistics it wanted for a given plan.

Biased Value Histograms for Accurate Estimates

Biased histogram entries store accurate counts and should be used to update statistics reflecting the number of rows obtained for that particular value.

The Optimizer can rapidly ascertain the number of retrieved rows by referencing the histogram’s bias value.

Regrettably, this is not always true.

I generated a test scenario by populating a table with 50,000 rows, each with one of 100 unique dates.

CREATE MULTISET TABLE Statistics_Estimation1
(TheDate DATE FORMAT 'YYYY-MM-DD' NOT NULL)
PRIMARY INDEX (TheDate);
INSERT INTO Statistics_Estimation1
SELECT DATE '2010-01-01' + (ROW_NUMBER() OVER (ORDER BY 1) MOD 100) AS TheDate
FROM SYS_CALENDAR.CALENDAR
SAMPLE 50000;

I executed the following statement to generate a new statistics index. The system can condense 50,000 rows into 100 histograms, which will be saved as biased values.

Ensuring the estimated number of rows matches the retrieved number of rows is crucial in our test setup.

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
COLLECT STATS ON Statistics_Estimation1 COLUMN(TheDate);

The histograms can be checked using the following statement:

SHOW  STATISTICS VALUES ON Statistics_Estimation1 ;

/** Biased: Value, Frequency **/
/* 1 */ DATE ‘2010-01-01’, 515,
/* 2 */ DATE ‘2010-01-02’, 498,
/* 3 */ DATE ‘2010-01-03’, 510,
/* 4 */ DATE ‘2010-01-04’, 482,
/* 5 */ DATE ‘2010-01-05’, 490,
/* 6 */ DATE ‘2010-01-06’, 518,
/* 7 */ DATE ‘2010-01-07’, 510,
/* 8 */ DATE ‘2010-01-08’, 512,
/* 9 */ DATE ‘2010-01-09’, 493,
/* 10 */ DATE ‘2010-01-10’, 513,
/* 11 */ DATE ‘2010-01-11’, 498,
/* 12 */ DATE ‘2010-01-12’, 513,
/* 13 */ DATE ‘2010-01-13’, 509,
/* 14 */ DATE ‘2010-01-14’, 500,
/* 15 */ DATE ‘2010-01-15’, 491,
/* 16 */ DATE ‘2010-01-16’, 489,
/* 17 */ DATE ‘2010-01-17’, 481,
/* 18 */ DATE ‘2010-01-18’, 508,
/* 19 */ DATE ‘2010-01-19’, 496,
/* 20 */ DATE ‘2010-01-20’, 505,
/* 21 */ DATE ‘2010-01-21’, 498,
/* 22 */ DATE ‘2010-01-22’, 496,
/* 23 */ DATE ‘2010-01-23’, 505,
/* 24 */ DATE ‘2010-01-24’, 504,
/* 25 */ DATE ‘2010-01-25’, 485,
/* 26 */ DATE ‘2010-01-26’, 508,
/* 27 */ DATE ‘2010-01-27’, 494,
/* 28 */ DATE ‘2010-01-28’, 507,
/* 29 */ DATE ‘2010-01-29’, 495,
/* 30 */ DATE ‘2010-01-30’, 501,
/* 31 */ DATE ‘2010-01-31’, 486,
/* 32 */ DATE ‘2010-02-01’, 517,
/* 33 */ DATE ‘2010-02-02’, 501,
/* 34 */ DATE ‘2010-02-03’, 505,
/* 35 */ DATE ‘2010-02-04’, 496,
/* 36 */ DATE ‘2010-02-05’, 514,
/* 37 */ DATE ‘2010-02-06’, 492,
/* 38 */ DATE ‘2010-02-07’, 496,
/* 39 */ DATE ‘2010-02-08’, 504,
/* 40 */ DATE ‘2010-02-09’, 486,
/* 41 */ DATE ‘2010-02-10’, 509,
/* 42 */ DATE ‘2010-02-11’, 490,
/* 43 */ DATE ‘2010-02-12’, 499,
/* 44 */ DATE ‘2010-02-13’, 487,
/* 45 */ DATE ‘2010-02-14’, 513,
/* 46 */ DATE ‘2010-02-15’, 517,
/* 47 */ DATE ‘2010-02-16’, 498,
/* 48 */ DATE ‘2010-02-17’, 494,
/* 49 */ DATE ‘2010-02-18’, 484,
/* 50 */ DATE ‘2010-02-19’, 500,
/* 51 */ DATE ‘2010-02-20’, 488,
/* 52 */ DATE ‘2010-02-21’, 483,
/* 53 */ DATE ‘2010-02-22’, 484,
/* 54 */ DATE ‘2010-02-23’, 497,
/* 55 */ DATE ‘2010-02-24’, 487,
/* 56 */ DATE ‘2010-02-25’, 493,
/* 57 */ DATE ‘2010-02-26’, 515,
/* 58 */ DATE ‘2010-02-27’, 479,
/* 59 */ DATE ‘2010-02-28’, 505,
/* 60 */ DATE ‘2010-03-01’, 518,
/* 61 */ DATE ‘2010-03-02’, 491,
/* 62 */ DATE ‘2010-03-03’, 484,
/* 63 */ DATE ‘2010-03-04’, 510,
/* 64 */ DATE ‘2010-03-05’, 511,
/* 65 */ DATE ‘2010-03-06’, 494,
/* 66 */ DATE ‘2010-03-07’, 493,
/* 67 */ DATE ‘2010-03-08’, 500,
/* 68 */ DATE ‘2010-03-09’, 531,
/* 69 */ DATE ‘2010-03-10’, 497,
/* 70 */ DATE ‘2010-03-11’, 512,
/* 71 */ DATE ‘2010-03-12’, 481,
/* 72 */ DATE ‘2010-03-13’, 500,
/* 73 */ DATE ‘2010-03-14’, 510,
/* 74 */ DATE ‘2010-03-15’, 487,
/* 75 */ DATE ‘2010-03-16’, 511,
/* 76 */ DATE ‘2010-03-17’, 515,
/* 77 */ DATE ‘2010-03-18’, 513,
/* 78 */ DATE ‘2010-03-19’, 517,
/* 79 */ DATE ‘2010-03-20’, 516,
/* 80 */ DATE ‘2010-03-21’, 516,
/* 81 */ DATE ‘2010-03-22’, 504,
/* 82 */ DATE ‘2010-03-23’, 493,
/* 83 */ DATE ‘2010-03-24’, 510,
/* 84 */ DATE ‘2010-03-25’, 486,
/* 85 */ DATE ‘2010-03-26’, 495,
/* 86 */ DATE ‘2010-03-27’, 497,
/* 87 */ DATE ‘2010-03-28’, 481,
/* 88 */ DATE ‘2010-03-29’, 501,
/* 89 */ DATE ‘2010-03-30’, 486,
/* 90 */ DATE ‘2010-03-31’, 512,
/* 91 */ DATE ‘2010-04-01’, 487,
/* 92 */ DATE ‘2010-04-02’, 501,
/* 93 */ DATE ‘2010-04-03’, 490,
/* 94 */ DATE ‘2010-04-04’, 508,
/* 95 */ DATE ‘2010-04-05’, 518,
/* 96 */ DATE ‘2010-04-06’, 478,
/* 97 */ DATE ‘2010-04-07’, 494,
/* 98 */ DATE ‘2010-04-08’, 503,
/* 99 */ DATE ‘2010-04-09’, 494,
/* 100 */ DATE ‘2010-04-10’, 512,

Now we execute the explain for the below query:

Explain SELECT TheDate FROM Statistics_Estimation1 WHERE TheDate BETWEEN DATE’2010-02-14′ AND DATE’2010-02-16′;

1) First, we do a single-AMP RETRIEVE step from
DWHPRO.Statistics_Estimation1 by way of the primary index
“DWHPRO.Statistics_Estimation1.TheDate = DATE ‘2010-02-14′” with
no residual conditions into Spool 1 (group_amps), which is built
locally on that AMP. The size of Spool 1 is estimated with high
confidence to be 510 rows (15,810 bytes). The estimated time for
this step is 0.01 seconds.
2) Next, we do a single-AMP RETRIEVE step from
DWHPRO.Statistics_Estimation1 by way of the primary index
“DWHPRO.Statistics_Estimation1.TheDate = DATE ‘2010-02-15′” with
no residual conditions into Spool 1 (group_amps), which is built
locally on that AMP. The size of Spool 1 is estimated with high
confidence to be 1,020 rows (31,620 bytes). The estimated time
for this step is 0.01 seconds.
3) We do a single-AMP RETRIEVE step from
DWHPRO.Statistics_Estimation1 by way of the primary index
“DWHPRO.Statistics_Estimation1.TheDate = DATE ‘2010-02-16′” with
no residual conditions into Spool 1 (group_amps), which is built
locally on that AMP. The size of Spool 1 is estimated with high
confidence to be 1,530 rows (47,430 bytes). The estimated time
for this step is 0.01 seconds.
4) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 1 are sent back to the user as the result of
statement 1. The total estimated time is 0.02 seconds.

The Optimizer obtains primary index values through single-AMP primary index access because the selected rows are significantly fewer than the total number of rows in the table.

Did you know that the estimated count is 1530 rows? It’s peculiar because the total of the three histogram values for the dates (2010-02-14, 2010-02-15, and 2010-02-16) amounts to only 1528 rows!

/* 45 */ DATE ‘2010-02-14’, 513,
/* 46 */ DATE ‘2010-02-15’, 517,
/* 47 */ DATE ‘2010-02-16’, 498,

513+517+498 = 1528

Additionally, the Optimizer has estimated 510 rows for each single-AMP access. Is it possible that a bug exists within the Optimizer?

No.

Internal optimization occurs, resulting in a slightly imprecise but faster estimation. The Optimizer aggregates histogram values and calculates the mean by dividing the sum by their quantity. As in our example:

1528/3 = 509,33

The ceiling value determines the even distribution of the estimation among the single-AMP estimations, such as 510 – 510 – 510. Consequently, the estimation is slightly inaccurate.

We can demonstrate this by increasing the retrieved row count, compelling the Optimizer to perform a full table scan rather than individual-AMP lookups.

SELECT TheDate FROM Statistics_Estimation1 WHERE TheDate BETWEEN DATE'2010-02-14' AND DATE'2010-02-28';

The plan changes when the date range is extended to 2010-02-28.

Explain SELECT TheDate FROM Statistics_Estimation1 WHERE TheDate BETWEEN DATE’2010-02-14′ AND DATE’2010-02-28′;

1) First, we lock DWHPRO.Statistics_Estimation1 for read on a
reserved RowHash to prevent global deadlock.
2) Next, we lock DWHPRO.Statistics_Estimation1 for read.
3) We do an all-AMPs RETRIEVE step from DWHPRO.Statistics_Estimation1
by way of an all-rows scan with a condition of (
“(DWHPRO.Statistics_Estimation1.TheDate >= DATE ‘2010-02-14’) AND
(DWHPRO.Statistics_Estimation1.TheDate <= DATE ‘2010-02-28’)”)
into Spool 1 (group_amps), which is built locally on the AMPs.
The size of Spool 1 is estimated with high confidence to be 7,438
rows (230,578 bytes). The estimated time for this step is 0.11
seconds.
4) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 1 are sent back to the user as the result of
statement 1. The total estimated time is 0.11 seconds.

Summing up the histogram values below confirms that the estimation of 7,438 matches the number of retrieved rows precisely.

/* 45 */ DATE ‘2010-02-14’, 513,
/* 46 */ DATE ‘2010-02-15’, 517,
/* 47 */ DATE ‘2010-02-16’, 498,
/* 48 */ DATE ‘2010-02-17’, 494,
/* 49 */ DATE ‘2010-02-18’, 484,
/* 50 */ DATE ‘2010-02-19’, 500,
/* 51 */ DATE ‘2010-02-20’, 488,
/* 52 */ DATE ‘2010-02-21’, 483,
/* 53 */ DATE ‘2010-02-22’, 484,
/* 54 */ DATE ‘2010-02-23’, 497,
/* 55 */ DATE ‘2010-02-24’, 487,
/* 56 */ DATE ‘2010-02-25’, 493,
/* 57 */ DATE ‘2010-02-26’, 515,
/* 58 */ DATE ‘2010-02-27’, 479,
/* 59 */ DATE ‘2010-02-28’, 505,

When analyzing an execution plan, strive to understand how the estimations are constructed. This skill improves with practice, allowing you to tackle statistical problems proficiently.

Also, check out:
Teradata Statistics – Case Study
Extrapolating Teradata Statistics for Equality Predicates

Alongside the detailed histogram, Teradata keeps a summary record – what summary statistics store and when the optimizer falls back to them.

Grouping Collections and Tuning Histogram Detail

Several statistics on one table can be collected in a single request by listing multiple COLUMN clauses:

COLLECT STATISTICS COLUMN (A), COLUMN (B), ..., COLUMN (Z) ON table_name;

Grouping gives the optimizer an opportunity to reuse scans or intermediate work across the statistics in the request, for example when a multicolumn statistic and its single-column components are collected together:

COLLECT STATISTICS COLUMN (A,B), COLUMN (A), COLUMN (B) ON table_name;

A single table scan is not guaranteed for every combination, data type or release. Compare grouped and separate collection on your workload and inspect the actual collection plan and resource use; do not assume a fixed scan count or a performance gain from the syntax alone.

USING options: SAMPLE, MAXINTERVALS and MAXVALUELENGTH

COLLECT STATISTICS USING SAMPLE n PERCENT COLUMN (column_name) ON table_name;
COLLECT STATISTICS USING MAXINTERVALS n COLUMN (column_name) ON table_name;
COLLECT STATISTICS USING MAXVALUELENGTH n COLUMN (column_name) ON table_name;

USING SAMPLE sets the sample percentage for an individual statistic instead of applying one global percentage to every sampled statistic. See sample statistics for when sampling is appropriate.

USING MAXINTERVALS sets an upper limit on the number of histogram intervals. Each interval records its most common value and that value’s row count. More intervals improve the chance of capturing localized concentrations of values: when a distribution has many sharp peaks and valleys within a narrow range of values, folding them into one interval distorts the estimate. MAXINTERVALS is an upper setting, not a promise that every histogram has that many equally sized intervals. The 250-interval default described for Teradata 14.00 is historical; check the valid range for your release.

USING MAXVALUELENGTH controls how many bytes of each value, starting from the leftmost character, the statistic keeps. Values that differ only beyond the retained prefix are treated as equal. Teradata 14.00 raised the limit from 16 to 25 bytes and stopped padding shorter character values with spaces; treat 25 bytes as historical context, not a current default, and check the saved statistic options before choosing a larger value. Bytes and characters are not interchangeable in multibyte character sets: a VARCHAR(2000) column does not by itself mean 2,000 retained characters. See why MaxValueLength matters for a worked discussion.

Join Estimation Without Statistics

What is Teradata Join Estimation?

This article demonstrates the functioning of Teradata Join Estimation in the absence of statistics. It presents the heuristics employed to estimate row count and emphasizes the importance of collecting statistics on all join columns.

Teradata Join Estimation Heuristics

The worst-case scenario involves joining two tables without any collected statistics. We create both tables from scratch and insert rows without collecting summary statistics at the table level. We populate the two tables by drawing from a dummy table that is the foundation for all our examples.

CREATE SET  TABLE DWHPRO.STATS1 ,FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO,
     MAP = TD_MAP1
     (
      PI INTEGER NOT NULL,
      COL1 INTEGER NOT NULL,
      COL2 INTEGER NOT NULL)
PRIMARY INDEX ( PI );

We add 50 rows to the stats1 table.

CREATE SET  TABLE DWHPRO.STATS2 ,FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO,
     MAP = TD_MAP1
     (
      PI INTEGER NOT NULL,
      COL1 INTEGER NOT NULL,
      COL2 INTEGER NOT NULL)
PRIMARY INDEX ( PI );

We add 150 rows to the stats2 table.

The SHOW STATISTICS output indicates that neither table has any statistics. Consequently, the Optimizer needs to estimate the cardinality of both tables by conducting a random AMP sample over the primary index.

SHOW STATISTICS VALUES ON STATS1;
[3624] There are no statistics defined for the table  
SHOW STATISTICS VALUES ON STATS2;
[3624] There are no statistics defined for the table  

We will execute a basic INNER JOIN of the two tables on an unindexed column.

EXPLAIN SELECT * FROM STATS2 inner JOIN STATS1 ON STATS1.COL1 = STATS2.COL1;

 1) First, we lock DWHPRO.STATS1 in TD_MAP1 for read on a reserved
      RowHash to prevent global deadlock.
   2) Next, we lock DWHPRO.STATS2 in TD_MAP1 for read on a reserved
      RowHash to prevent global deadlock.
   3) We lock DWHPRO.STATS1 in TD_MAP1 for read, and we lock
      DWHPRO.STATS2 in TD_MAP1 for read.
   4) We do an all-AMPs RETRIEVE step in TD_MAP1 from DWHPRO.STATS1 by
      way of an all-rows scan with no residual conditions into Spool 2
      (all_amps), which is duplicated on all AMPs in TD_Map1.  The size
      of Spool 2 is estimated with low confidence to be 96 rows (2,400
      bytes).  The estimated time for this step is 0.00 seconds.
   5) We do an all-AMPs JOIN step in TD_Map1 from Spool 2 (Last Use) by
      way of an all-rows scan, which is joined to DWHPRO.STATS2 by way
      of an all-rows scan with no residual conditions.  Spool 2 and
      DWHPRO.STATS2 are joined using a dynamic hash join, with a join
      condition of ("COL1 = DWHPRO.STATS2.COL1").  The result goes into
      Spool 1 (group_amps), which is built locally on the AMPs.  The
      size of Spool 1 is estimated with no confidence to be 600 rows (
      27,000 bytes).  The estimated time for this step is 0.01 seconds.
   6) Finally, we send out an END TRANSACTION step to all AMPs involved
      in processing the request.
   -> The contents of Spool 1 are sent back to the user as the result of
      statement 1.  The total estimated time is 0.01 seconds.

The execution plan forecasts a result set with approximately 600 rows, while Table Stats1 is anticipated to contain roughly 96 rows. Due to table replication across 2 AMPs, this number must be halved. As such, the Optimizer predicts 48 rows in Table Stats1, which aligns closely with the row count of 50.

To get the number of estimated rows in Table Stats2, we can use the following trick:

EXPLAIN SELECT * FROM STATS2;
1) First, we lock DWHPRO.STATS2 in TD_MAP1 for read on a reserved
      RowHash to prevent global deadlock.
   2) Next, we lock DWHPRO.STATS2 in TD_MAP1 for read.
   3) We do an all-AMPs RETRIEVE step in TD_MAP1 from DWHPRO.STATS2 by
      way of an all-rows scan with no residual conditions into Spool 1
      (group_amps), which is built locally on the AMPs.  The size of
      Spool 1 is estimated with low confidence to be 156 rows (5,148
      bytes).  The estimated time for this step is 0.01 seconds.
   4) Finally, we send out an END TRANSACTION step to all AMPs involved
      in processing the request.
   -> The contents of Spool 1 are sent back to the user as the result of
      statement 1.  The total estimated time is 0.01 seconds.

The execution plan shows an estimated row count of 156.

How is the estimate for a result set of 600 rows determined? The estimate is based on the following heuristic approach:

SQRT(number of rows of the bigger table) * (Number of rows from the smaller table). The result is rounded up to the nearest whole number. (Many thanks to Diether Noeth, who gave me this information in the Teradata forum):

Let’s do the math for our example:

SQRT(156) = 12,489996 * 48 => 599,519808, rounded to 600.

If current statistics on the cardinality of the tables exist, these are used for the heuristic:

COLLECT STATS COLUMN(PI) ON STATS1;
COLLECT STATS COLUMN(PI) ON STATS2;

EXPLAIN SELECT * FROM STATS2 inner JOIN STATS1 ON STATS1.COL1 = STATS2.COL1

EXPLAIN SELECT * FROM STATS2 inner JOIN STATS1 ON STATS1.COL1 = STATS2.COL1;
 1) First, we lock DWHPRO.STATS1 in TD_MAP1 for read on a reserved
      RowHash to prevent global deadlock.
   2) Next, we lock DWHPRO.STATS2 in TD_MAP1 for read on a reserved
      RowHash to prevent global deadlock.
   3) We lock DWHPRO.STATS1 in TD_MAP1 for read, and we lock
      DWHPRO.STATS2 in TD_MAP1 for read.
   4) We do an all-AMPs RETRIEVE step in TD_MAP1 from DWHPRO.STATS1 by
      way of an all-rows scan with no residual conditions into Spool 2
      (all_amps), which is duplicated on all AMPs in TD_Map1.  The size
      of Spool 2 is estimated with high confidence to be 100 rows (
      2,500 bytes).  The estimated time for this step is 0.00 seconds.
   5) We do an all-AMPs JOIN step in TD_Map1 from Spool 2 (Last Use) by
      way of an all-rows scan, which is joined to DWHPRO.STATS2 by way
      of an all-rows scan with no residual conditions.  Spool 2 and
      DWHPRO.STATS2 are joined using a dynamic hash join, with a join
      condition of ("COL1 = DWHPRO.STATS2.COL1").  The result goes into
      Spool 1 (group_amps), which is built locally on the AMPs.  The
      size of Spool 1 is estimated with no confidence to be 613 rows (
      27,585 bytes).  The estimated time for this step is 0.01 seconds.
   6) Finally, we send out an END TRANSACTION step to all AMPs involved
      in processing the request.
   -> The contents of Spool 1 are sent back to the user as the result of
      statement 1.  The total estimated time is 0.01 seconds.

The estimated result set has increased to 613 rows. Let us execute the query to see if the estimate has improved.

SELECT * FROM STATS2 inner JOIN STATS1 ON STATS1.COL1 = STATS2.COL1
-> SELECT completed. 0 rows returned.

The query produces no results, indicating that collecting statistics at the table level alone did not improve accuracy.

Comparing both execution plans can be confusing. In the first plan (without statistics), the retrieval step shows low confidence. In the second plan (with statistics), the retrieval step shows high confidence — yet the low-confidence estimate from the first plan was actually more accurate.

This is the introductory section on Join Estimations. Subsequent articles will delve into statistical analysis and its impact on the Optimizer’s estimates.

What to Collect: Rules by Index and Table Type

Statistics help the optimizer choose an access path (an index or a full table scan) and a join strategy: the join order, the join method (for example merge join or product join) and how rows are prepared, either duplicated to all AMPs or redistributed by the row hash of the join columns, sometimes followed by a sort on row hash. Each preparation step has a cost, and statistics let the optimizer weigh them. Primary index access is preferred even without statistics because of its efficiency.

Statistics also allow plans that are more efficient but riskier. A hash join performs best when the smaller table fits entirely in memory and poorly when it does not; without statistics, the optimizer may avoid the hash join to reduce the chance of a bad plan.

A collected statistic describes the table row count, rows per value, distinct values, average row length, NULLs per column and skew. Column statistics are stored as histograms, and table-level figures come from summary statistics. Teradata creates only as many intervals as it needs: a column with ten distinct values produces ten intervals. Collecting statistics describes skew; it does not reduce it.

Teradata does not define statistics for you, but its AutoStats framework (Viewpoint as the scheduler, plus DBC tables and procedures) can identify the statistics the optimizer considers beneficial and automate their collection.

Collected statistics versus dynamic AMP sampling

  1. Small tables always need collected statistics. On a 300-AMP system, a five-row table leaves most AMPs empty. A dynamic AMP sample can land on an empty AMP, the optimizer then assumes the table is empty, and the result can be a product join and a poor plan. Collection on small tables is cheap.
  2. Unique indexes (UPI, USI) guarantee unique keys, not perfectly even AMP distribution. Dynamic AMP sampling, sampled collection or full collection can each be appropriate, depending on predicates, distribution, correlation and cost. Collect full statistics when the index columns are used in non-equality (range) predicates.
  3. Non-unique indexes (NUPI, NUSI): the skew of the table or index subtable decides whether full statistics are needed. For a NUSI, the optimizer assumes one subtable index row per value; where that is not true, dynamic AMP sampling on the NUSI gives wrong results.
  4. Non-indexed columns in WHERE conditions: collect full or sample statistics, depending on whether a sample represents the data adequately. Without them the optimizer falls back on the heuristics shown at the start of this guide.

Row-partitioned tables

  1. Full statistics on the dummy column PARTITION cheaply tell the optimizer which partitions are empty and how many rows each holds. It uses this when costing joins such as the sliding-window merge join.
  2. Collect statistics on partitioning columns where they improve the workload’s estimates, not because a checklist says every column is mandatory.
  3. If the partitioning columns are not part of the primary index, the same primary index value can appear in several partitions. Also collect statistics on PARTITION plus the primary index columns: this tells the optimizer how many partitions a primary index value spans, which it uses for dynamic partition elimination and sliding-window merge joins. Together with the PARTITION, primary index and partitioning-column statistics, it is also used to cost rowkey-based merge joins.

Multicolumn statistics

  1. The optimizer assumes that single-column statistics are independent. Collect a multicolumn statistic when columns are strongly correlated; see when a multi-column statistic earns its keep.

A starting list of candidates

Begin with candidates that follow from the physical design, then keep only those the workload justifies:

  • SUMMARY statistics for non-partitioned tables; PARTITION statistics for partitioned tables
  • Primary index columns, as a combination
  • Secondary index columns, as a combination
  • Partitioning columns

Then add usage-driven candidates: columns used to join the table to others, and columns frequently used in WHERE conditions. Weigh cost against benefit for each. Date and time columns used in filters, joins or partition elimination can be important even though they change daily; code, type or flag columns that follow-up queries hardly use can often be left out.

Incremental Planning and Execution (IPE)

Teradata IPE

Teradata traditionally generates execution plans using information available during query optimization. This information includes statistics and cost factors, such as storage devices, CPU models, and available memory.

The optimizer generates multiple execution plans and selects the one with the lowest cost.

Teradata 14.10 introduces Incremental Planning and Execution (IPE), which utilizes available information at query optimization and collects additional data during query execution.

IPE – Splitting the Execution Plan into Fragments

To gain greater statistical insight, the optimizer divides requests into “request fragments” rather than creating the entire execution plan simultaneously.

A “fragment execution plan” is generated for every request fragment, and the output of each plan fragment is passed on to the next one.

One query plan fragment may lead to a spool table where statistical information such as the number of rows can be fed into the execution of the subsequent plan fragment.

The following describes historical IPE support. Eligibility, cost thresholds, query-band controls and dynamic planning features depend on release and configuration; it is not a current universal feature list.

  • Uncorrelated scalar subqueries
  • Single‑row accesses for UPI or USI

This is an example of an uncorrelated scalar subquery, which returns a single row and is not joined to another table:

SELECT * FROM
TheTable t01 INNER JOIN TheTable2 t02 ON t01.Key = t02.Key
WHERE
t01.Col1 > (SELECT MIN(t03.Col1) FROM TheSubTable t03)
;
 

The optimizer can use IPE for the above statement by splitting the request into two request fragments:

Fragment 1
(SELECT MIN(t03.Col1) FROM TheSubTable t03);

Fragment 2

SELECT * FROM
TheTable t01 INNER JOIN TheTable2 t02 ON t01.Key = t02.Key
WHERE
t01.Col1 > [Result of Fragment 1];

Feedback from a prior request fragment can activate sophisticated optimization methods, such as partition elimination.

The method employed for uncorrelated scalar subqueries is analogous to “hard coding” a predicate value within our query, thus enabling the optimizer to implement static partition elimination.

The technique discussed here generates execution plans that are referred to as “dynamic”.

The one-minute and parsing-cost thresholds described in older examples are release-specific, not universal current defaults. Check the supported IPE controls for the deployed release and inspect DYNAMIC EXPLAIN before relying on them.

New query bands enable us to compel the optimizer to implement IPE for simple queries.

IPE Query Bands

Query bands can be utilized to either enable or disable IPE usage for eligible requests with estimated execution times under one minute.

– The system should determine eligibility (the default behavior):

SET QUERY_BAND = 'DynamicPlan=SYSTEM;' FOR SESSION;

– Bypasses the cost thresholds (one minute execution time). If IPE is available, Teradata will use it:

SET QUERY_BAND = 'DynamicPlan=SYSTEMX;' FOR SESSION;

– Turns off IPE for the session:

SET QUERY_BAND = 'DynamicPlan=OFF;' FOR SESSION;

How can we know if IPE is available?

Teradata has introduced a new option for the EXPLAIN modifier: DYNAMIC EXPLAIN. This option is needed to determine whether the optimizer uses the static or dynamic (IPE) execution plan.

Teradata displays the dynamic plan when the query is eligible for IPE; otherwise, it shows the static plan.

It is not necessary to always use DYNAMIC EXPLAIN to verify the execution plan because Teradata must execute every fragment in order to display the plan.

Starting from Teradata 14.10, the EXPLAIN modifier offers visibility on IPE utilization.

  • Eligible for IPE:
    This request is eligible for incremental planning and execution (IPE). The following is the static plan for the request.
  • Eligible for IPE but does not meet cost thresholds (run time < 1 minute):
    This request is eligible for incremental planning and execution (IPE) but does not meet cost thresholds. The following is the static plan for the request.
    If the request is not eligible for IPE, the output of the EXPLAIN modifier will not mention it (i.e., the explained plan looks like in pre-Teradata 14.10 releases)

IPE and Workload Management

TASM does not fully support IPE as it enforces workload rules solely on the static plan, with workload exceptions being dependent on the dynamic plan.

Teradata 15.00 enables TASM to distinguish between requests that utilize IPE and those that do not, allowing for a straightforward categorization based on this distinction.

Statistics Extrapolation

Object-use counts and DML/UDI change tracking have different purposes. Available counters and optimizer extrapolation behavior depend on release and configuration. The historical examples below do not establish exact change counts or universally improved estimates.

CREATE TABLE TheDB.TheTable
(
PK INTEGER NOT NULL,
TheCol CHAR(01)
) PRIMARY INDEX (PK);

INSERT INTO TheDB.TheTable
SELECT
ROW_NUMBER() OVER (ORDER BY 1) AS PK,
TRIM(CASE WHEN RANDOM(1,1000) <= 500 THEN 'A' ELSE 'B' END) AS TheCol
FROM
(
SELECT NULL AS X FROM TheDB.AnyBigTable SAMPLE 200000
) x;

COLLECT STATISTICS ON TheDB.TheTable COLUMN(PK);
COLLECT STATISTICS ON TheDB.TheTable COLUMN(TheCol);

Teradata Statistics Extrapolation with up to version 13.10

EXPLAIN SELECT * FROM TheDB.TheTable  WHERE TheCol  = 'C';

 3) We do an all-AMPs RETRIEVE step from TheDB.TheTable by way of an all-rows scan with a condition of ("TheDB.TheTable.TheCol =  'C '") into Spool 1 (group_amps),
which is built locally on the   AMPs.  The size of Spool 1 is estimated with high confidence to be  20,000 rows (42,580,000 bytes).
The estimated time for this step is 0.31 seconds.

We choose a value in the WHERE clause that is absent from the queried column. The optimizer endeavors to prevent underestimating the table’s cardinality.

The heuristic applied in this instance assumes that 10% of the rows in the result set will be present, even with high confidence. However, in this case, the accurate estimation is that there will be 0 rows. The rationale behind this approach is straightforward: overestimating the table size is preferable to underestimating it, as it will result in a conservative execution plan.

Teradata Statistics Extrapolation starting with Teradata version 14.10

DML/UDI tracking can inform estimation where supported, but its accounting and refresh behavior are release-dependent. It does not replace statistics or guarantee accurate selectivity for every value.

The historical plan estimates one row; that is not a returned-row count. Compare the actual query result separately, and do not infer exact cardinality from confidence labels.

3) We do an all-AMPs RETRIEVE step from TheDB.TheTable by way of
an all-rows scan with a condition of ("TheDB.TheTable.TheCol = 'C '") into Spool 1 (group_amps),
which is built locally on the AMPs.  The size of Spool 1 is estimated with high confidence to be 1 row (1,079 bytes).
The estimated time for this step is 0.04 seconds.

Evaluate Object Use Counts for the databases and workloads where usage tracking will inform statistics maintenance. Collection, caching, persistence, and optimizer use depend on the release and configuration; measure their overhead before enabling them broadly. Disabling Object Use Counts does not make the optimizer depend exclusively on dynamic AMP sampling: collected statistics and other available estimation mechanisms still matter.

Extrapolation only helps while the row count still moves; what happens when statistics go stale but the table does not grow is a separate problem.

Maintaining Statistics: Usage Logging and Recollection Thresholds

Four questions dominate statistics maintenance: which statistics to collect, where in the ETL/ELT process to collect them, how often, and which existing statistics are never used. Recollecting after every transformation step on the same target table keeps estimates accurate but costs resources; waiting until all steps finish risks sub-optimal plans in between. Tables that need daily and weekly recollection force either separate jobs or a trade-off between resource use and estimate accuracy. Since Teradata 14.10, logging options and recollection thresholds address these questions directly. For a manual, register-based approach, see how often to collect statistics.

Find unused statistics with object use counts

Enable object use counts (OUC) for the database. Statistics are database objects like tables and views, so every access to a statistic is then recorded in DBC.ObjectUsage:

BEGIN QUERY LOGGING WITH USECOUNT ON TheDatabase;

Three timestamps show whether a statistic earns its keep:

  • When OUC was activated: DBC.DBQLRuleTbl.TimeCreated
  • When the statistic was created: DBC.StatsTbl.CreateTimeStamp
  • When the optimizer last used it: DBC.ObjectUsage.LastAccessTimeStamp, the most important of the three

For a tested example with DBC.StatUseCountV, see tracking statistics usage with StatUseCountV.

Find missing statistics with STATSUSAGE and XMLPLAN logging

STATSUSAGE and XMLPLAN logging are defined for a user, not a database; only USECOUNT is allowed on a database (attempting it fails with error 3706):

BEGIN QUERY LOGGING WITH XMLPLAN, STATSUSAGE ON TheUser;

XMLPLAN records the optimizer’s plan and estimates. It is not a runtime measurement: logging actual execution rows is a separate capability that must be configured and verified. STATSUSAGE is less comprehensive but easier to work with because it is tabular, and it is a better basis for tuning than the session-level DIAGNOSTIC HELPSTATS ON FOR SESSION;.

Let thresholds decide when to recollect

Teradata judges whether a statistic is stale from UDI counts (updates, deletes and inserts, tracked as part of OUC; updates are tracked at column level) together with the statistic’s history of earlier histograms. Do not drop statistics before recollecting them, or that history is lost.

Thresholds can be left to Teradata, which is the recommended approach, or set explicitly:

COLLECT STATISTICS USING THRESHOLD 14 DAYS AND THRESHOLD 5 PERCENT
    COLUMN (TheColumn) ON TheDatabase.TheTable;

With these options, recollection happens when the change exceeds 5 percent or the statistic reaches 14 days of age: AND joins the option clauses and does not require both conditions. A thresholded COLLECT must still be scheduled and still has overhead when it skips collection, and thresholds do not detect every stale distribution. Evaluate collection overhead and cardinality estimates on the actual workload.

Check Constraints and Row Estimates

Without Teradata CHECK Constraint

When a non-indexed column is utilized in a retrieval operation (i.e. WHERE statement) and lacks Teradata Check Constraints, the Optimizer employs heuristics to approximate the number of rows in the resulting set by inferring missing statistics.

The estimated number of rows is 7,342, equivalent to 10% of the table’s total rows. This heuristic is utilized by the Teradata optimizer when statistics on the non-indexed column are not available.

CREATE TABLE Heuristics_Without_Check
(
	PK INTEGER NOT NULL,
	GENDER BYTEINT NOT NULL
) PRIMARY INDEX (PK);

INSERT INTO Heuristics_Without_Check
SELECT
	ROW_NUMBER() OVER (ORDER BY 1) AS PK,
	RANDOM(0,1)
FROM SYS_CALENDAR.CALENDAR;
-> 73,414 Rows inserted

--> Collect PI Statistics to have the real row number:

COLLECT STATISTICS COLUMN(PK) ON Heuristics_Without_Check;

SELECT * FROM Heuristics_Without_Check WHERE GENDER = 0;

Explain SELECT * FROM Heuristics_Without_Check WHERE GENDER = 0;

  1) First, we lock DWHPRO.Heuristics_Without_Check in TD_MAP1 for read
     on a reserved RowHash to prevent global deadlock.
  2) Next, we lock DWHPRO.Heuristics_Without_Check in TD_MAP1 for read.
  3) We do an all-AMPs RETRIEVE step in TD_MAP1 from
     DWHPRO.Heuristics_Without_Check by way of an all-rows scan with a
     condition of ("DWHPRO.Heuristics_Without_Check.GENDER = 0") into
     Spool 1 (group_amps), which is built locally on the AMPs.  The
     size of Spool 1 is estimated with no confidence to be 7,342 rows (
     190,892 bytes).  The estimated time for this step is 0.03 seconds.
  4) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.
  -> The contents of Spool 1 are sent back to the user as the result of
     statement 1.  The total estimated time is 0.03 seconds.

But with a simple trick, the estimates can be massively improved even if the non-indexed column statistics are missing. The Optimizer uses existing CHECK constraints to optimize the estimates.

With CHECK Constraint

The following formula is used for this:

Estimated Rows = Total Rows x (1 / Possible Unique Values)

Since the RANDOM function we use to load our test table only returns the values 0 and 1, we can add the following CHECK constraint to our test table:

CREATE TABLE Heuristics_With_Check
(
	PK INTEGER NOT NULL,
	GENDER BYTEINT NOT NULL CHECK (GENDER IN (0,1))
) PRIMARY INDEX (PK);

INSERT INTO Heuristics_With_Check
SELECT
	ROW_NUMBER() OVER (ORDER BY 1) AS PK,
	RANDOM(0,1)
FROM SYS_CALENDAR.CALENDAR;
-> 73,414 Rows inserted

--> Collect PI Statistics to have the real row number:

COLLECT STATISTICS COLUMN(PK) ON Heuristics_With_Check;

Applying the formula above produces the following estimated row count for the result set.

Estimated rows = 73,414 * (1 / 2) = 36,707 rows

Examining the Execution Plan:

SELECT * FROM Heuristics_With_Check WHERE GENDER = 0;

Explain SELECT * FROM Heuristics_With_Check WHERE GENDER = 0;

  1) First, we lock DWHPRO.Heuristics_With_Check in TD_MAP1 for read on
     a reserved RowHash to prevent global deadlock.
  2) Next, we lock DWHPRO.Heuristics_With_Check in TD_MAP1 for read.
  3) We do an all-AMPs RETRIEVE step in TD_MAP1 from
     DWHPRO.Heuristics_With_Check by way of an all-rows scan with a
     condition of ("DWHPRO.Heuristics_With_Check.GENDER = 0") into
     Spool 1 (group_amps), which is built locally on the AMPs.  The
     size of Spool 1 is estimated with low confidence to be 36,707 rows
     (954,382 bytes).  The estimated time for this step is 0.07 seconds.
  4) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.
  -> The contents of Spool 1 are sent back to the user as the result of
     statement 1.  The total estimated time is 0.07 seconds.


For the roughly uniform random fixture, the CHECK domain can improve the estimate. It does not establish uniform frequencies: in the 1,000-row skewed fixture, the estimate was 500 for either of two permitted values while actual counts were 900 and 100. Collect relevant statistics for skewed distributions.


SELECT COUNT(*) FROM Heuristics_With_Check WHERE GENDER = 0;
--> 36,711 rows

A Warning when using CHECK constraints

Do not define CHECK constraints solely to improve estimates. Keep in mind that validating the constraints requires extra processing and does not generally create a secondary index.

More information about Teradata CHECK constraints you can find here:
Check Constraints explained in Detail

Five shorter pieces cover individual parts of this in more detail: the practical rules for what to collect, judging whether a statistic was worth collecting, reading StatsUsageCountV to find unused statistics, how extrapolation fills the gap between collections and why some skew is natural and some is an artefact.

Two more on measuring what the optimizer knows: pulling query statistics out of the system tables and a worked example of statistics gone wrong.

Two more on collection itself: when a multi-column statistic earns its keep and the basics of how Teradata stores a statistic.

Articles in this guide

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.

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