Archives of the TeradataForum

Message Posted: Thu, 16 Apr 2009 @ 12:21:55 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

The one "supposed" advantage of collecting at the column level versus the index level is that if you collect at the column level, the stats will survive if the index is dropped. However, that is not entirely true.

If you collect stats on a single-column Secondary Index using INDEX, and then drop the INDEX, the statistics remain on the DBC.TVFields table.

The same is true if you collect stats using COLUMN. So, there is no difference between to using COLUMN or INDEX when collecting on a single- column SI because the stats will persist after the index is dropped regardless of which method you use.

If you create a multi-column Secondary Index, and then collect stats on it using COLUMN, and then drop the INDEX, the stats are dropped. The same is true if you collect stats using INDEX. In each case the stats are stored on DBC.Indexes and in both cases they are dropped with the index. So, again, there is no difference in specifying COLUMN or INDEX, because the stats will NOT persist regardless of which method you use.

The only case where it may be helpful to collect on the COLUMN versus the INDEX is if you first collect stats on multiple columns using COLUMN, and THEN create a multi-column Secondary Index on those same columns. In this case the stats are stored as multi-column stats (on the DBC.Indexes table), and not as an index (because the index did not exist when you collected the stats). Then, if you drop the index, the stats will persist.

Thanks.

Dirk Anderson
Architect
Enterprise Information Management

	<Prev	Next>		<<First	<Prev	Next>	Last>>