Collect Stats difference

UDA
Enthusiast

Collect Stats difference

Is there any difference in performance when -
(1) We collect stats on Column
(2) We collect stats on Index

Say table...

COLLECT STATS ON INDEX A (ACCNO);
COLLECT STATS IN COLUMN A (ACCNO);

A is a table with UPI as ACCNO.

Thanks
2 REPLIES
Teradata Employee

Re: Collect Stats difference

Other than syntax, there is no difference between collecting stats on an index or on the same column(s). The same is true of the "CREATE INDEX form" versus "Legacy form" of COLLECT STATS statement.

The actual collection process (which you can EXPLAIN), the data stored in the dictionary tables, and the subsequent use of the stats for query optimization are all the same.
Teradata Employee

Re: Collect Stats difference

There is a small difference in how the statistics collection process will read the data, depending on whether the column is indexed or not.

If there is a NUSI defined on the column, the statistics collection process will scan the index subtables, instead of the base table. Depending on the number of distinct values in the NUSI, this could contribute to a reduced time to collect the statistics because fewer rows and fewer data blocks will need to be read.