Query on Collect stats

Database
Enthusiast

Query on Collect stats

Hi All,

Can some one explain I should take sats seperately on the columns or I shall take multicolumn stats for below query.And Why?

Select * from
Tab1 T1
Inner Join
Tab2 T2
On T1.Col1=T2.Col1
and T2.Col2=T2.Col2;

Whether the stats should be taken as

Collects stats on Tab1 column(Col1,col2); and Collects stats on Tab2 column(Col1,col2);

OR

They should be taken seperately like?

Collects stats on Tab1(Col1);
Collects stats on Tab1(Col2);
Collects stats on Tab2(Col1);
Collects stats on Tab2(Col2);

Thanks.

1 REPLY
Enthusiast

Re: Query on Collect stats

First, I'm guessing that's a typo in your query and the last line should read and T1.Col2 = T2.Col2; ;)

Carrie Ballinger has some good blog posts on this site about statistics and multi-column statistics. I'd recommend you take a look at those for a pretty detailed analysis of statistics recommendations.

One option is to try one approach, and check the explain plan. If you see an improvement in the confidence, great. If not, drop the stats and try the opposite approach.

If the Col1, Col2 combination is used to filter quite frequently, collect on both columns together (and look at making that an index).