Archives of the TeradataForum

Message Posted: Tue, 17 Jul 2012 @ 23:49:56 GMT

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

PI need not be 100% unique. And in general, the more columns in the PI, the less likely it will be useful for access / joins. So if data skew is not a problem, a "fairly unique" PI is often the best choice. And if you can use the same PI for related tables, that's even better. On the other hand, average number of rows with the same PI value should be "relatively low" and maximum number of rows with the same PI should be "reasonable".

I'm being intentionally vague and not stating what would be an acceptable number of PI duplicates, because it depends. Note that it will be much lower for SET tables (vs. MULTISET) since the overhead of duplicate row checking on insert/update grows very rapidly as the number of rows with the same RowHash increases. And with a PPI where the partitioning column is not part of the PI, you can generally tolerate more PI duplicates - as long as you get partition elimination.

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