Archives of the TeradataForum

Message Posted: Tue, 19 Oct 2010 @ 13:11:40 GMT

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

Hi,

This is one of those common mis-understandings in the Teradata world.

"DELETE ALL" was originally intended as a syntax which confirmed to the optimiser that you wanted 'fast path' delete, however that was changed years ago.

So, for current releases of Teradata (current = anything from the last 10-15 years or more)

     DELETE FROM t1;

     DELETE FROM t1 ALL;

The above two statements will give ** identical ** results from the perspective of both functionality and performance.

Or to put it another way, from the perspective of functionality and performance there is NO difference between the two.

You asked about writing Transient Journal rows. It is the ABSENCE of a WHERE clause which causes the optimiser to generate AMP steps that do NOT write to the TJ. The 'ALL' keyword is irrelevant to the optimiser. I suspect it is only accepted so that (very) old code does not break.

Cheers,

Dave

Ward Analytics Ltd - Information in motion ( www.ward-analytics.com )

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