Archives of the TeradataForum

Message Posted: Thu, 08 Jul 2010 @ 12:23:37 GMT

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

<-- Anonymously Posted: Wednesday, July 07, 2010 12:52 -->

Hi Rishi,

Sorry, the responses to your questions were cut off in my last post. Please see below. Thanks!

--------------------------------------------------------

Per the Teradata Subject Matter Expert, you can also refer to the following book: Teradata Orange Book on Partitioned Primary Index Usage (Single-Level and Multilevel Partitioning). For additional information on MLPPI, see Teradata Database Database Design and SQL Data Definition Language manuals.

1. Do I need to use both MLPPI columns in my query in order to make use of PPI or partition will be used even if the first DATE column is used in the filter condition?

[SME] Partition elimination can occur for the first, second or both.

2. How can I find out from explain plan how many partitions are being used. When I am using DATE only as a filter condition, explain plan shows partitions as double the number of DATES used ("2 partitions", 4 partitions..). When DATE is used along with AREA_CODE (single or multiple), it says as many partitions as many DATEs. Does that mean that partitions are not used when only single column is used?

[SME] The explain plan indicates the number of combined partitions out of the total combined partitions (the product of the number of partitions defined at each level) that are used.

If a single DATE is used as a filter, you should get 20 partitions (meaning 20 combined partitions). Not sure why you are getting multiples of 2 (the actual DDL and query with explain would need to be provided to understand this further). If a single DATE and a single AREA_CODE is used as the filter, should only be 1 partition.

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