Do I REALLY Have to Recollect Stats When I Upgrade to Teradata 12?

Blog
The best minds from Teradata, our partners, and customers blog about whatever takes their fancy.
Teradata Employee

You’re in a time crunch. Maybe you’ve got an undersized backup environment that is incredibly slow, or you just have important stuff happening and you need to keep the software upgrade window as short as possible. You’ve been instructed to fully re-collect stats immediately after upgrading to Teradata 12 (T12), and you know there’s good stuff in the new T12 statistics, but, hey, life’s full of trade-offs. So what awaits you should you upgrade and just let your normal recollection routines refresh statistics over time?

Mostly, you can get away with this, although, admittedly it runs against the grain of what we (and I myself) would recommend. The Teradata 12 database will make use of your 6.2 stats just fine, so it's not as if your old stats will be totally useless.

But if you don’t recollect immediately, you will miss out on some of the enhancements to statistics in T12. A big one is that with T12 you will have twice as many intervals in the histograms, once you recollect. This is valuable if there is any level of skew in the data, because the optimizer will have 200 instead of 100 frequent values with exact numbers of rows that carry those values. Or if you have really big skew or very few distinct values, you can carry twice as many of those values (and their detail) in the high-biased intervals. Also, interval zero has some new fields, and you’ll miss out on their advantages. All of these things are designed to make your plans better.

Which leads to me my use of the word “mostly” in the paragraph above. If you have multicolumn stats with partial nulls in them, try to get those stats recollected as quickly as possible after the upgrade. Make those recollections your priority. The new T12 code assumes that the number of unique values for a multi-column statistic is adjusted based on partial nulls. The calculation of partial nulls requires the new T12 histograms.

             

In conclusion, I feel I must re-iterate the party line before I sign off. While redoing all your stats immediately after an upgrade is not an absolute must, you could get better plans by doing so sooner, rather than later. In addition, an upgrade is a good sync point, and a good time to make everything in the environment as good as it can be.  And by the way, make sure you have fully recollected stats on T12 before attempting any evaluation or performance comparison pre- and post-upgrade, or you’ll get me in big trouble.

1 Comment
I appreciate the insight you've shared. We're preparing for release12 and will add an entry to the plan for statistics gathering.