Archives of the TeradataForum

Message Posted: Fri, 22 Nov 2002 @ 18:48:38 GMT

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

Ok, let's start out with a couple of facts about index statistics:

First of all, for single column indices, there's no difference in the storage representation of the statistics between column and index level statistics. They're both kept in columns in TVFields. For compound indices, the index statistics are kept on columns in the table indexes.

Now for the question... Access by a single column of a multicolumn index won't use the index under any circumstances that I'm familiar with. Statistics on the single column in addition to the index may give the optimizer enough distribution information to reject some options in a complex plan, but I don't think it'll cause the optimizer to use the index if all components of the index are not referenced.

You might try indexing the columns individually. If they're moderately selective individually, there's a chance that the index will be used when referencing the columns individually (in this case statistics on the index/column - remember they're the same for single column indexes will help). When referenced together, the optimizer may use the indices individually to produce bit maps for each index and do a bit map set manipulation(BMSM). This technique isn't quite as fast as a single index access, but it's a whole lot faster than a full table scan.

Anyway, I'm inclined to collect statistics on every column that I'm likely to use in a predicate.

Pat Belle
Teradata Certified Master

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