Statistics collection


Statistics collection

I have few doubts regarding statistics collection.

1. Does the order of columns used in collect stats command important? 

2. Consider the following scenario 

Table A joins with Table B based on Col1 and Col2 and Table A joins with Table C based on Col1, Col2 and Col3.

So if I do collect stats table A column (col1, col2, col3) is that enough to provide statistics details required for both queries (A join B and A join C)

or do I need to again collect stats based on Col1 and Col2 seperately besides collecting collectively on col1, col2 and col3.

How is the statstics information stored collectively on all the cols or seperately on the cols mentioned in the collect stats?

Thanks, Raam


Re: Statistics collection

Hi Raam,

please see the resonses below-

1) the order of the columns doesnt matter during collecting stats on more than one column like

collect stats on table_nm column (a,b)

collect stats on table_nm column(b,a)

Both of the above statements are would lead to same results so its not required to use both of these together.

2)  You will have to collect stats on column(col1,col2) for table a for joining with table b and  collect stats on column (col1,col2,col3) for joining with table c as these are used in this combination..

If col1 and col2 are also seperately joined then you will have to collect stats on them individually like

collect stats on table1 column col1;

collect stats on table1 column col2;

Regarding the storage of collect stats in dbc views :

Statistics are stored in DBC views like ColumnStats,IndexStats and MultiColumnStats

hope this helps..