Archives of the TeradataForum

Message Posted: Thu, 06 May 2010 @ 08:33:13 GMT

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

We have a sales transactions table that is currently partitioned on the sales date, one partition per week.

This gives good performance for the bulk of our reports and queries.

We have a requirement to produce some new reports on the current day's sales, these are likely to be run frequently throughout the day and the expectation is that the response time will be very short. We aren't currently able to achieve this.

To facilitate these reports I was thinking of switching to a Multilevel Partitioned Primary Index, with the second level being the day of the week. This will necessitate adding an extra column to the table and including the day of week in the queries but we can do this.

I haven't used MPPIs before. I was wondering if there would likely be performance impacts on for the bulk of our existing queries that filter on week(s), as they would have to hit 7 small partitions instead of the one larger partition.

Is there likely to be any significant difference between the MPPI I've described and just having a PPI on the sales date with one partition per day? Each would mean a 7-fold increase in the number of partitions.

Would full table scan queries be affected by having the MPPI - they would read the same amount of data, but would having 7x the number of partitions to scan make it slower?

How about loads? Any likely impact there?

I'm happy to test it and see the results - but was interested in any experiences people have had before me.

The other alternative I was considering was a sparse join index - recreating it each day, for sales on the current day. The downside to this is they aren't compatible with multi-load and we are currently multiloading this table during the day.

Any suggestions, observations or recommendations gratefully received.

Thanks

Chris.

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