Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Wed, 12 Jun 2002 @ 12:37:30 GMT

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

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.

  <Prev Next>   <<First <Prev Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 28 Jun 2020