Archives of the TeradataForum

Message Posted: Mon, 27 Jun 2011 @ 09:56:52 GMT


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

Subj:		Re: MLPPI on date field

From:		Dieter Noeth

Anomy.Anom wrote:

Is there no downside to having more partitions? Would everyone then partition by a single day rather than week or month then?

You easily hit the limit of 64k partitions when you start using multi-level partitioning, so this forces less-than-daily partitions.

Would having more partitions have any negative impact on full table scans, or secondary-index lookups, or uniqueness checks or anything else?

AFAIK for FTS/SI-lookup/USI-maintenance there's no impact.

But if the partitioning column is not part of the PI:

- any PI access without specifying the partitioning column is slower than before because every partition has to be probed to find the matching RowHash. The performance impact correlates to the number of partitions (you could add an NUSI to avoid that).

- joins to non-partitioned tables using the PI only without specifying the partitioning column will be slower. The performance impact correlates to the number of partitions participating in the join (i.e. after partition elimination)

The potential performance improvements of partitioning might get larger when you got more partitions, but those potential disadvantages, too :-)

So just create as much partitions as neccessary (based on your access pattern).

Dieter


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

Attachments

Library

Quick Reference