COLLECT STATS on combination of columns than single column

Database
Highlighted
Enthusiast

COLLECT STATS on combination of columns than single column

Hi ,

Need recommendation for collecting stats

TB1.COL1 = TAB2.COL1
AND
TB1.COL2 = TAB2.COL2

Recommendation 1:

COLLECT STATS TB1 COLUMN (COL1,COL2);
COLLECT STATS TB2 COLUMN (COL1,COL2)

Recommendation 2:

COLLECT STATS TB1 COLUMN (COL1);
COLLECT STATS TB1 COLUMN (COL2);
COLLECT STATS TB2 COLUMN (COL1);
COLLECT STATS TB2 COLUMN (COL2);

Which is Recommended either Recommendation 1 or Recommendation 2 and why?

Thanks,
Siree
4 REPLIES

Re: COLLECT STATS on combination of columns than single column

REcommendation 1
Stat must be on join column, like index.
Junior Contributor

Re: COLLECT STATS on combination of columns than single column

Hi Siree,
submit a "diagnostic helpstats on for session" and explain the query.
At the end of the explain there will be the recommendation of the optimizer.

Dieter
Enthusiast

Re: COLLECT STATS on combination of columns than single column

Hudson & Dieter ,

Thanks for you Inputs.

I Ran diagnostic helpstats on session : I got recommendation 1

What is the main diff between recommendation 1 & recommendation 2, If we collect STATS recommendation 1 OR recommendation 2. How optimizer will interpret?

Ideally which one is recommended?

Thanks,
Siree
Enthusiast

Re: COLLECT STATS on combination of columns than single column

Hi Siree,

In your case Recommendation 1 work better than Recommendation 2.

Because you are using, combination of two cols from two tables as a joining conditions so the stats on the same combination from those tables works better than the individual stats.

Thanks,
Sen