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