Collect stats on single and multiple columns together - Performance advantage

Database
Enthusiast

Collect stats on single and multiple columns together - Performance advantage

Hi All,

I wanted to know what would be the behavior of the Teradata system in case of the following collect stats and select scenarios.
#

Collect stats on each column of the composite primary index separately. What would happen in cases of sql requests with where/join clauses on all the primary index columns together and only one of the columns in where/join conditions.

#

Collect stats on each column of the composite primary index together. What would happen in cases of sql requests with where/join clauses on all the primary index columns together and only one of the columns in where/join conditions.

#

Collect stats on each non primary index column separately. What would happen in above two scenarios listed

#

Collect stats on each non primary index column together. What would happen in above two scenarios listed

Also in which cases the collect stats will impact the performance of a query negatively. I have seen this some times.We are not able to identify differences in performance in different cases for different sizes of tables after testing these scenarios on live systems of Teradata 12 and 13 versions. All we need is in which cases the statistics will be used by optimizer in the above cases exactly. Also we are not able to identify effect of collect stats on secondary and join indexes. Can you please give a simple system side summary or direct me to any samples or overview articles.

Thanks in advance
1 REPLY
Enthusiast

Re: Collect stats on single and multiple columns together - Performance advantage

See my response (which references some articles found on here): http://teradataquestions.com/questions/343/collect-stats-on-single-and-multiple-columns-together/344#344