I get this error while collecting stats:
5,781 You cannot collect statistics on more than 32 groups of non-indexed columns.
I have a table with NUPI on col1. I am trying to collect stats on this table and getting this error.
What i understand is that, you cant have number of stats on table which doesn't contain col1 as one of the columns, more than 32. However, in my table i already have 40 such stats which doesn;t contain col1 as one of the columns. Now when i add one more stats without col1 , i get this error. However, should not i have got this error when i was collecting stats on 33rd such stats.
I am confused now. Pls help is this is little urgent.
You can't have more than 32 multi-column stats unless they are collected on a multi-column index.
Why do you think you need all those stats?
Multi-column stats are mainly for multi-column joins (FKs) or when all of them are ANDed together in a WHERE-condition.
Actually , this is a existing table , around 2.5 TB. We have changed the PI from 4 columns to only 1 column. When we tried to collect old stats, we got this error.
regarding single column and multi column stats : Say i have a multi column stats on col1,col2,col3,col4. All four of them are used in a where clause together.
In TD 13 onwards, if i take single column stats on these columns individually, will that serve the purpose or i still need to take the multi column stats if they are used together in where clause.
We can drop some multi column stats if required for this table as we already have single column stats for some of multi column stats.
There are some good articles on this topic:
I always try to avoid large/multi-column stats because of the 16-byte limit (and if i have to collect it i prefer SAMPLE). TD14 with the increased limits might change that, but i'd try to stick with SAMPLE.