Re: Big table data truncate or delete
Posted by: Pawel Wtorek
Date: August 18, 2022 08:23AM

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


Options: ReplyQuote

Written By
February 23, 2022 07:57AM
Re: Big table data truncate or delete
August 18, 2022 08:23AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.