Archives of the TeradataForum

Message Posted: Mon, 06 Jun 2005 @ 07:35:27 GMT

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

Hello,

Regarding the quote from the PPI Orange Book:

" Consider having only as many date ranges as needed currently plus some for the future. Limiting ranges to currently needed ones helps the Optimizer cost plans better and allows for more efficient primary index access, joins, and aggregations when the partitioning column is not included in the primary index. A good rule-of-thumb may be to define 10% or less of the partitions to be empty partitions for future dates. "

The phrase "when the partitioning column is not included in the primary index" seems to indicate, that empty partitions is a potential problem only in that case?

At our site, we had (have) a problem with a join involving a date-range on a PPI-table, the date-column is the partitioning column and it's part of the PI, and we have lots of (future) empty partitions.

The plan decided to redistribute the data from the (few) qualifying partitions and do a product join on date. We wanted either a star-join (rowkey-based), or a product join using DPE. Changing the PPI-table to having only a few empty partitions didn't change the plan. In both cases the optimizer had a very accurate estimate of the number of rows from the PPI-table (as recommended, we collect stats on the partitioning-column after every load).

We run R5.1.2

In general, I think a lot of attention has been given to the case where the partitioning column is not part of the PI.

Maybe so much, that performance for the case when the partitioning column is indeed part of the PI, is a little less than optimal?

Best Regards

Ole Dunweber
Coop Nordic

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