Archives of the TeradataForum

Message Posted: Thu, 29 Mar 2007 @ 17:18:59 GMT

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

Collecting statistics on system-derived column PARTITION is done once per PPI table, and it is not related to any particular column in the table. It is a very quick operation because rather than reading all the base table rows, it usually just scans the cylinder index for that PPI table.

Under some conditions, when partitions start or end in the middle of a data block, the statistics collection process may have to read a few of the base table data blocks, but the number of data blocks read will never be more than the number of partitions minus one.

If re-collection is performed at the table level, PARTITION will be included in that re-collection effort.

It tells the optimizer how many partitions are empty and other statistical information about the partitions. It is only used for costing plans, particularly when joins to other tables are involved. It is not used to bypass the processing of certain partitions. In generating a plan, the optimizer cannot assume a partition is empty because statistics say it is empty, since data may have been added since the statistics were collected.

Collecting statistics on the "partitioning column" will build a complete histogram and provide more detail when the PPI table is being selected based on a range of partitioning column values.

Thanks, -Carrie

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