Archives of the TeradataForum

Message Posted: Thu, 18 Dec 2003 @ 14:27:52 GMT

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

I've been doing some stuff with PPI and its proving to be pretty reliable and (especially on very large tables) gives great gains. Things to bear in mind are as follows :

1. Like all indices they can be a bit temperamental if you do not exactly match data types when you query against them. An example of this is if you wanted to calculate a date on the fly, the PPI will not always be used. (this is being fixed by NCR I think.)

2. Check out the purge options. This is a big gain as if you purge a partition with delete you get lightening quick delete performance.

3. In your specific example, you cannot do this type of thing yet. Stuff must be hard coded (no working out on the fly) and you need to use dynamic DDL to tweak the partitions as you go forward (you can modify and add and drop as described in 2 above.) I think this is a little like sparse indices, they don't like you working stuff out on the fly. also remember the 65K limit.

4. For what you describe there is an alternative. If you want to split in months why not use many tables with a UNION ALL view? In V2R4 this was not very good as it had to check all tables but in V2R5 you can use check constraints to deal with this. If you create your month tables with a check constraint on the date that means only that months dates are allowed in that table, the optimizer will look at the UNION ALL view and only go to the table for the month you specify in SQL. The real bonus of this is that you can partition the table once using this method (e.g. by date) and then partition again with PPI (say on a typ field or an indicator using a CASE partition.) It allows you to do a double horizontal partition. This gives real performance gains and (from work I've done) is reliable and doesn't take too many SQL tweaks to get the optimizer to use it.

The only aggravation with PPI and check constraints is the need for dynamic DDL for month end purge and roll over but there are many ways to handle that. As with all new stuff, make sure you prototype and tweak based on real query workload, don't shoe horn something in thats not appropriate. Its really worth having a good look at the V2R5 release notes to fully understand how PPI, constraints etc can give you gains. There is a lot there that if used correctly makes overall machine performance much better.

Hope this helps. Cheers and Happy Christmas to everyone.

Andy

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