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?
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 :