Archives of the TeradataForum

Message Posted: Thu, 13 Dec 2001 @ 17:22:58 GMT

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

If I am understanding your problem correctly, you might consider a join index between the two tables. This should improve your performance but it would complicate your current maintenance strategy.

For example, you could create a join index on the history table that would have only the most recent few days of history. The affect should be a "sparse table" containing only the last few days of history. I would think that this would improve your performance. It might also be possible to create this join index as an outer join between the two tables to get all of the recent customer activity in one pre- joined sub-table.

Be aware that you cannot Multiload a table with a join index. You would have to drop the join index and re-create it every time you did your multiload. In this case, that would be desirable because, if your join index is date dependent, you would want to create the join index to always get the last few days of history. To that extent, you need to evaluate the cost of building the join index against the benefit of the query improvement. Also, be aware of the locks that a join index build place on the underlying tables. You also need to consider the space consumption of the join index sub-table.

I am not a join index expert, so I'd be curious to here from others if this is a viable strategy or if there is a problem somewhere. But I think it might be something worth investigating. I've used join indices before, but never in this manner.

Regards,

Thomas F. Stanek
TFS Consulting

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