Delete / truncate command

Database

Delete / truncate command


I understand there is no truncate command in Teradata.
Truncate command just moves the pointer at the end of the table to the beginning and marks space it used to available and since it just moves the pointer and does not record the rows removed, this is such a superfast command. This also does not keep the stats it had.

I know that "DELETE databasename.tablename ALL" is fast, but does it record the rows it deleted and is this the onlt fast command available to do this or is there a superfast method to achieve the same and maybe lose the stats it had also..?

3 REPLIES

Re: Delete / truncate command

In general, the unqualified delete statement uses a "superfast" method similar to what you described for the truncate command (and essentially the same as for drop table) whenever it can do so without violating transaction semantics. The method requires that the transaction be committed immediately after execution of the delete statement. This condition is satisfied when the delete is submitted in "BTET" mode without an explicit transaction, but not if the delete is submitted within an explicit transaction or in ANSI mode sessions.
N/A

Re: Delete / truncate command

Jim,

It was my experience to use DELETE all and then drop table for very large tables.

If you try dropping a very large table, I believe all the activity will be logged in TJ.
While DELETE ALL does not log activity in TJ and seems to be fastest.

What do you think?

Vinay

Re: Delete / truncate command

No, DROP TABLE is very fast, regardless of table size. It does not log individual rows.