Archives of the TeradataForum

Message Posted: Tue, 03 Apr 2012 @ 10:57:05 GMT

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

Hi Mark,

"Delete all" will be quicker, although being practical there often isn't a lot of difference in the performance of them.

However, especially for a process that is run regularly (say a daily batch process) then I recommend the "delete all" approach. This will do less work as it only removes the data and leaves the definition in place. Remember that if you remove the definition then this requires accessing multiple dictionary tables, and of course you then have to access those same tables (typically) when you re-create the object.

Apart from the performance aspect, the downside of the drop/create approach is that every time you create an object Teradata inserts "default rows" into the AccessRights table, even if subsequent access to the object is controlled via Role security and/or database level security. As you may well know the AccessRights table can easily get large and very skewed. In my experience many sites have a process which cleans this table on a regular basis, removing redundant rows. If your (typically batch) processes regularly drop/create objects, then you're simply adding rows into the table which have previously been removed by a clean process, and which will be removed in the future by the same process. This all sounds like a complete waste of time to me.

And finally.. (my apologies if you're already aware of this) remember that the "ALL" keyword is NOT required to achieve the quick (aka "fast path") delete of all rows in a table.

Cheers,

Dave

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

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