Archives of the TeradataForum
Message Posted: Wed, 12 Jun 2002 @ 12:37:30 GMT
| Subj: | | Re: Delete vs delete all |
| |
| From: | | Craig Ferry |
The following is what NCR told me was the difference:
The optimizer recognizes that a DELETE ALL that IS NOT inside an uncommitted unit of work will not journal the deleted rows in the
transient journal. The intelligence behind the decision was that even if the system restarts the transact has be previously authorized to
run to completion - eliminate all the rows; therefore since the deleted rows will never need to be "restored", the system skips the overhead
of copying them into the transient journal. Actually all the DELETE ALL does is to re-chain the internal data blocks on the "data block
free-chain" - something that is not effected by the number of rows in the table.
A DELETE with a WHERE clause will always copy the deleted rows into the transient journal - hence more overhead and the overhead is
directly proportional to the number of rows deleted - even if it IS NOT inside an uncommitted unit of work. This is done to ensure data
integrity in the event of a restart or the requestor ABORTS the request.
| 
