Diagnostic Help stats for session

Database
Enthusiast

Diagnostic Help stats for session

When we run diagnostic help stats on for session and see the explain of the query it give the recommandation for COLLECTING the stats on the combinations of the COLUMNs

But we are collecting stats on every individual column weekly basis ,

So why i am getting this recommandation in Diagnostic . is it the necessary recommandation also from where does it get this information even though stats has been collected.?
2 REPLIES
Enthusiast

Re: Diagnostic Help stats for session

Statistics on individual columns can be used to guess multicolumn statistics only.

Just imagine data like this:
colA,colB
------------------
A,B
C,D
------------------

colA:
A - 50pct
C - 50pct
colB:
B - 50pct
D - 50pct

Guess for (colA,colB):
(A,B) - 25 pct
(A,D) - 25 pct (never existed)
(C,B) - 25 pct (never existed)
(C,D) - 25 pct

Enthusiast

Re: Diagnostic Help stats for session

When does collection of multi-column statistics help?

suppose my query is

select from
t1 join t2 on t1.c=t2.c where t1.a=10 and t1.b=5;

what will be your recommendation of collection of statistics for t1.
What will help in this case?
(a) collection of stats on a and b individually
(b) collecting multi-column stats on (a,b)

How are statistics used in this case?