Delete ALL

[prakhar]

Hi,
We all know that delete ALL doesn't require transient journal so it is faster than normal delete.
Could anyone please explain how a rollback would happen in case a DELETE ALL transaction fails in middle.
We are changing our delete operations to delete all to make them faster...so as a part we have to analysis pros and cons of DELETE ALL.....Help will be greatly appreciated...

[prakhar]

can anybody reply on this???

[Jim_Chapman]

DELETE ALL doesn't need to be rolled back if the transaction fails "in the middle" because the physical delete is not done until the transaction commits. That is why the delete optimization is only used when a delete statement is known to be the last (or only statement in a transaction).

If a system restart occurs during the commit processing (when the physical delete is being done) the operation is completed by transaction recovery.

By the way, this optimization is used for any delete statement that does not have a WHERE condition. The ALL keyword has no significance.

[prakhar]

Hi Jim,
"DELETE ALL doesn't need to be rolled back if the transaction fails "in the middle" because the physical delete is not done until the transaction commits"
Not able to get this statement.......

Could you please explain how it really goes in this case...(Delete or delete all)
Suppose i have a table with 1.2 million rows and i ran
Delete all from tableA;
In between the system restarts how the delete will take place???

Regards,
Prakhar Agarwal

[Jim_Chapman]

When executing the DELETE statement, Teradata simply flags the transaction as having a "deferred action" and writes a record describing the required action into the transient journal. At commit time, an "End-1" is written to the TJ, indicating that the transaction is committed but not yet completed. Then the TJ is scanned to identify and execute all the deferred actions. Finally, an "End-2" is written to the TJ, indicating that all deferred actions have been completed.

In the event of a system restart, if a tranaction is found to have the End-1 but the End-2 is missing, then transaction recovery will identify and complete all the pending deferred actions.

[prakhar]

Hi Jim,
thankx for the reply...could you please tell where to find these details told by you....?
Any documentation.......