Say i have an Primary index XX(col1,col2) on table. I can see that stats has been collected on col1,col2 as single columns. But not as a combination of column i.e col1,col2.
So, does it hamper the performance as the index is on both the columns so a stats on both column combined should have been collected ? or collecting on single column has the same affect ?
Hashing output of Col1 and col2 columns separately would always be different from the combined HASH output of (Col1,col2) so in my opinion the Combined stats would be more relevant for the optmizer to get the data demographics other wise the optmizer would rely upon random sampling of (col1, col2) which might differ from the actual data distribution across the AMPs.
The link below would give you a fair amount of idea about stats collection.
I have a question regaring the query. The single column statistics are stored in dbc.tvfields and the statistics of the indexes (even multicolumn) is stored in dbc.indexes.
If i have stats on multicolumn but they are not indexes, they are not stored in dbc.tvfields and since they are not indexes, they are stored in dbc.indexes. So, where are they stored ?
I wish to find list of tables having no indexes defined