Collect Stats Multi-Column

Database

Collect Stats Multi-Column

Hi,

Is 2nd statement redundant?

COLLECT STATISTICS column (dept,emp_no) on Test.Employee;

COLLECT STATISTICS column (emp_no) on Test.Employee;

Does multi-column stats collection really collect more stats/information then collecting all the columns seperately. I understand that multi-column statemet would only require to read the table one time, so collect stats process itself would run faster. However, is there any other benefit beyond that i.e during query plan/execution?

yk

Tags (1)
1 REPLY

Re: Collect Stats Multi-Column

The second statement is not redundant.

Statistics from the first statement would be used to determine the cardinality and value distribution when both the dept and emp_no are used in a constraint, join, or aggregation.

Statistics from the second statement would be used to determine the cardinality and value distribution when only the emp_no (and not the dept) is used in a constraint, join, or aggregation.