Archives of the TeradataForum

Message Posted: Wed, 08 Dec 2004 @ 13:41:34 GMT

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

Be very careful when planning deletes from PPI tables - we've had some interesting experiences with it recently. Specifically we don't see consistent behaviour in the use of 'fast path' delete processing. A lot of the time you would expect Teradata to logically delete partitions when it actually scans the partitions, performing a normal partial table delete (with the associated use of the Transient Journal - that's what really hit us). We saw similar problems with both a standard delete and an 'alter table' syntax.

Currently we are on V2R5.0.3.21 (if memory serves) and are using standard delete syntax;

     delete table_1
     where date_column between  and ;

and having (limited) success in having Teradata choose a fastpath delete with no Transient Journal usage. However, once the range between the lower and upper date gets past a certain point (this is not consistent and I think related to the volume of data between the ranges) we revert to a partition scan and delete using the TJ. If we need to change the partitioning we would delete as above then alter the PI as below.

The moral of this (rather long winded) tale is to test your solution to within an inch of it's life, and get as much info from your Teradata service rep as possible on your specific release/patch and the way it handles PPI deletes.

Stuart

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