CODEWITHSUNDEEP
teradata
BellevueBob
BellevueBob

Reputation: 9618

Should statistics be collected on a UNIQUE PRIMARY INDEX in Teradata?

A long time ago, I was told that I should not collect statistics on on a Teradata UPI, but I never understood the reason. It may have been related to the version we were running at that time. It also may have been that a UPI doesn't need stats so collecting them is a waste of time.

My question is: should I continue that practice now that we are using TD 13.10 (and soon moving to TD 14)? If so, does the size of the table make a difference, such as a 1000-row AVT table versus a 100-million row detail table, both having a single-column UPI widely used by join operations?

Any specific references to Teradata documentation will be appreciated.

Upvotes: 0

Views: 2621

Answers (1)

dnoeth
dnoeth

Reputation: 60482

You should collect UPI stats for very small tables, for big tables it depends on the usage pattern: If it's only for joins you don't need it, because the optimizer already knows about uniqueness. Only when you have WHERE-conditions on that column beside equality stats might be neccessary, but then SAMPLE STATS are ok.

For each release there's an "official" recommendation by Carrie Ballinger, e.g.

http://developer.teradata.com/blog/carrie/2012/04/teradata-13-10-statistics-collection-recommendations

Upvotes: 5

Related Questions