Because fastpath delete processing defers the delete until a transaction ends and because it allows rollback and delete processing to be almost instantaneous, make sure that the DELETE or DELETE ALL statements are positioned properly based on the active session mode and the transaction type.
| Session mode | Transaction Type | Positioning Requirements |
|---|---|---|
| ANSI | ANSI | The DELETE ALL or unconditional DELETE must be in the same multistatement request that contains a COMMIT. For example, DELETE FROM table_name ALL ;COMMIT; |
| Teradata | Implicit (such as a multistatement macro or BTEQ request) |
The DELETE ALL or unconditional DELETE must be the last statement that references the target table in the request. |
| Explicit | The DELETE ALL or unconditional DELETE must be in the same multistatement request that contains the END TRANSACTION that terminates the currently open transaction. For example, BEGIN TRANSACTION; DELETE FROM table_name ALL ;END TRANSACTION; This is not valid for embedded SQL applications because the DELETE and END TRANSACTION statements must fall into the same request, and embedded SQL does not support multistatement requests. |