Teradata Statistics Extrapolation for DATE Columns

2
582
teradata statistics extrapolation

teradata statistics extrapolationWhat is Teradata Statistics Extrapolation?

A quite usual scenario in data warehousing is the daily population of snapshot historized tables, with data valid for the most recent date.

Here is a typical example:

SELECT Snapshot_Date,Count(*) CNT FROM The_Snapshot_Table;

Snapshot Date CNT
2015-04-10 10
2015-04-11 15
2015-04-12 10
2015-04-13 25
2015-04-14 15
2015-04-15 30

— We assume in our example, that statistics are collected on 2015-05-15, after the daily snapshot for 2015-01-15 was loaded:

COLLECT STATISTICS ON THE_SNAPSHOT_TABLE COLUMN(SNAPSHOT_DATE);

After loading 2015-04-16, and without extrapolation, for queries accessing snapshot date 2015-04-16, the optimizer would expect zero rows to be returned (assuming statistics are not recollected). For example, the optimizer would expect 0 rows to be returned in the query below:

SELECT * FROM The_Snapshot_Table WHERE Snapshot_Date=DATE’2015-04-16′

Extrapolation on DATE columns allows the optimizer to make reasonable estimations in such cases, even when statistics are stale. The optimizer considers date extrapolation only if it assumes that the current statistics are stale.

Which is Statistics Information extrapolated?

The optimizer is extrapolating the following statistical information:

  • The number of table rows, based on the available statistics summary information and random-AMP sampling
  • The number of distinct primary index values and the maximum value per statistic histogram
  • The number of rows per value for the date column

 

How is the Statistics Information extrapolated?

It is important to understand that the optimizer only estimates a certain number of dates into the future (future in this context means any date after the last date for which collected statistics exist). The highest estimated date used for extrapolation is based on a random AMP sample. In the extrapolation process, the random AMP sample gives information about the number of distinct future dates.

Below extrapolation formula is used to calculate the highest future date:

Maximum Extrapolated Date = Maximum Date with Collected Statistics + ((Maximum Date with Collected Statistics – Minimum Date with Collected Statistics) * ((Distinct Random AMP Sample Dates * Average Rows Per Date from Collected Statistics) / (Distinct Dates from Collected Statistics * Average Rows Per Date from Collected Statistics))

Here is an example with 1000 Average Rows per Date from Collected Statistics, the random AMP sample is assumed to return 10 different future dates:

Maximum Extrapolated Date = 2015-03-30 + ((2015-03-30 – 2015-01-01) * ((10 * 1000) / (89 * 1000)) = 2015-04-09

teradata statistics extrapolation

 

Assuming that our query selects a date range between 2015-03-25 and 2015-04-09, the estimated number of rows will be:

Estimated Rows from Collected Stats + Estimated Rows from Extrapolation: (6*1000) + (10*1000)  = 16000 Rows.

As we can see, the estimation is much better than without extrapolation (the optimizer would only have estimated 6000 rows from the collected, but stale statistics).

The quality of the estimation based on a random AMP sample highly depends on the skew factor of the table.

The excellent case is when the random AMP sampling delivers all distinct future dates, but a skewed table may result in an entirely wrong estimation of different future dates.

Another important influence factor is how the extrapolated dates are covering the date range of your query.

The optimal situation is if the collected statistics plus the extrapolated statistics include the query date range entirely. Even if only a part of the extrapolated statistics includes your query, the overall estimated statistics will be better than without statistics extrapolation.

Only in the case that your query date range is not covered by the extrapolated statistics at all, there is no advantage from extrapolation

teradata statistics extrapolation

 

Although statistics extrapolation can be very helpful in the case of snapshot tables which are loaded on a daily base, this should not be an excuse to stop collecting statistics all. Extrapolation helps you to move your recollection process to a point in time when your Teradata system is less loaded.

 

Our Reader Score
[Total: 6    Average: 5/5]
Teradata Statistics Extrapolation for DATE Columns written by Roland Wenzlofsky on April 20, 2015 average rating 5/5 - 6 user ratings

2 COMMENTS

  1. You are right regarding point (1) and i always wondered why this may be the reason? My assumption was that during the process it’s easier as “division by zero” handling can be avoided?

    Regarding point (2), as far as I remember, the calculation formula is available in the official documentation.

  2. Hi Roland,
    Interesting article, thanks. I have a couple of points / questions:
    1) (minor point), AFAIK the optimiser never estimates ‘0 rows’, th eminimum value it uses is 1 row or ‘1 row per amp’.
    2) (question) where did you get the extrapolation calculation from?
    Cheers,
    Dave

LEAVE A REPLY

Please enter your comment!
Please enter your name here