1.Suppose if collect stats is created on table , we can delete the data of the table by using delete statement those collect stats also be deleted or not?
Ans: No collect stats will not be deleted on table. We need to drop it manually by using DROP COLLECT STATISTICS statement.
2.Where these Collect statistics are actually stored?
Ans: Collect Statistics are stored in DBC views like IndexStats,ColumnStats and MultiColumnStats.
3.What is the purpose of Collect Statistics Command?
Ans: The purpose is to gather and store demographic data for indices or join index or one or more columns of a table. If you want optimizer to use high confidence, You should create COLLECT STATISTICS on columns or indices
@Where these Collect statistics are actually stored?
ReplyDeleteon single column or single index stats will be stored in dbc.tvfields
and
multi column and multiindex stats will be stored in dbc.indexes