Archives of the TeradataForum

Message Posted: Fri, 22 Oct 2004 @ 17:17:16 GMT

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

You shouldn't assume that the optimizer will choose bit mapping if you drop the composite NUSI. Even if it does, building the bitmap takes some additional time & resources for each query. A composite NUSI will give you better performance than a corresponding bit-mapping step. How much better, and whether that performance gain is worth the cost of maintaining another index, will be specific to your environment.

In general, bit mapping is chosen because none of the indexes is selective enough to be useful by itself. So in your specific example of only three NUSIs, two single-column NUSIs and a single composite NUSI on the same two columns, if dropping the composite NUSI does cause a bit-mapping step to be generated then keeping the composite NUSI and dropping both the single-column NUSIs would probably have been a better choice.

If the PI is unique, collecting stats on the PI of a large table is unlikely to improve performance. For a NUPI, collecting stats may significantly improve performance for certain queries; the larger the variance or skewing in number of rows for different PI values, the more likely it is that you need to collect statistics.

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