Re: Big table data truncate or delete

[Pawel Wtorek]

sql truncate command:

a) TRUNCATE works much faster than DELETE
b) TRUNCATE generates a negligible amount of redo and undo
c) Transaction log - one log indicating page deallocation (removes data, releasing allocation of data pages used for storing table data and writes only page deallocations to the transaction log) - faster execution than DELETE.
d) Performance (acquired lock) - table and page lock - does not degrade performance during execution


sql delete command:

a) DELETE works much slower than TRUNCATE
b) DELETE generates a small amount of redo and a large amount of undo
c) Transaction log - for each deleted record (deletes rows one at a time and records an entry in the transaction log for each deleted row) - slower execution than TRUNCATE.
d) Performance (acquired lock) - record lock - reduces performance during execution - each record in the table is locked for deletion

regards