Archives of the TeradataForum

Message Posted: Thu, 02 Dec 2004 @ 22:09:30 GMT

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

Another reason a larger number of smaller partitions may not be the best choice is if you are doing some amount of primary index access on the PPI table. If the primary index column(s) are not defined as part of the partitioning key, and the query does not pass a partitioning key value, in such a case each partition will be probed individually, looking for a match on the NUPI value. This is still a single-AMP operation, but the time to probe each partition can add up. I tried this type of access on a test system with a table partitioned by day (2500 total partitions, 300 million rows in the table) and it took over 20 seconds to complete the primary index lookup. With the same PPI table partitioned by month (about 80 partitons), the same query was sub-second.

In this situation, if you wanted to keep the daily partitioning, you could create a USI on the PI column if it is truly unique, or a join index if it is not, and that would eliminate the probing overhead.

Thanks, --Carrie

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