Archives of the TeradataForum

Message Posted: Mon, 23 Oct 2006 @ 16:40:40 GMT

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

Sorry, I should have been more specific - I use diagnostic helpstats when running the Explain. The Explain requested collecting multiple columns on the JI, so I created the secondary index using the set of columns to satisfy the need.

The overhead of JI maintenance occurs during off hours in a few minutes so I didn't do a full cost benefit analysis, since report execution speed is a very high priority and the use of this JI is core to most queries.

I have not seen a single Explain with language that indicates that the secondary index on the JI is engaged. Query plans went from not using the JI to using them in the manner that we expected that they should be.

I haven't experimented with removing the secondary index after stats are collected. The statistics appear as if they were collected on the JI columns. This leads me to consider the possibility of using the secondary as a tool to collect stats - after completing the collection process just drop it. I'll post findings if I get a chance to do this scenario this week.

Cheers,

Jack

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