Archives of the TeradataForum

Message Posted: Tue, 02 Mar 2004 @ 18:38:43 GMT

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

Here is the view of a Teradata insider that I consulted.

"It depends on the PPI definition and the queries. If definition is a RANGE_N only for the non-empty partitions (and maybe a small percentage more), the optimizer has a better count of how many partitions there could be. Statistics can give the optimizer an estimate of what is empty but the plan has to be based such that it works even if they not empty (that is, has to work for the maximum defined). My conjecture it would be better not have more than 10% of your partitions empty so that total number of partitions is not significantly different than the number of empty partitions. In many cases, it won't make a difference though.

It might be illuminating to do performance/explain analysis to see what certain cases do.

If the purpose of having a lot of partitions into the future is avoid having to ALTER as often, that might not be a good idea. I would think it is better to advance partitions at least one a month. If you do that you only need to go a few weeks into the future. Doing this makes you get into a regular routine of doing this (and probably automate it). If you only have to do this rarely (like once a year), one might forget to do it. Also, limiting the ranges make sure data (for instance, with an incorrect date) is rejected that shouldn't be going into table."

Mark

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