Collecting Statistics

[Liam1]

Hello,

We are having a bit of a 'discussion' in our office...

Wht does COLLECT STATS actually do?

If I have a column (Col_1) on a table (Tab_1) - does the collect stats basically carry out:

Select
Count (*)
,Col_1
From
Tab_1
Group by 2;

Or does it do more complex things?

You can use "collect stats on Tab_1 COLUMN Col_1;
even if the column happens to be an INDEX... so...
is there a differnce in collecting stats on a column as opposed to an index?

Thanks in advance,

Liam.

 

[Patten]

Collect statistics gathers information about the data demography of a column, index, table, etc. It is being used by the parser to determine the optimal way to carry out a query. It means as well one has to recollect these statistics after major changes in the data demography of a table. If you collected statistics on a table with over a million rows, afterwards you empty the table completely without recollecting statistics, the parser will still think the table contains 1 million rows, and perhaps make wrong decision in the way it wants to carry out a query against this table.

 

[Liam1]

Thanks for the reply,
I know why we do what we do and how it is used, I was just curious as to how it actually calculates these statistics.

It knows how many unque values are in a column, is it more complicated than that? What is the difference between an column and an index (in sats terms)

Thanks,

Liam1