What creates the most volume of REDO entries: INSERT, UPDATE, or DELETE?

From: dana <dana_at_work_at_yahoo.com>
Date: Fri, 17 Jul 2009 12:39:57 -0700 (PDT)
Message-ID: <e7fdad4e-9dec-4f6d-a31b-5676f70801c3_at_c2g2000yqi.googlegroups.com>

Which of these 3 operations creates the most volume of total REDO log file entries; not REDO log *records*: INSERT, UPDATE, or DELETE? A colleague of mine says INSERTS add more to the redo log file. His rationale? the redo log contains all changes to the database; and an entire row takes the most redo log file space to store and re-play if the database goes down.

In practice, it seems like DELETEs create larger REDO log files; have seen servers crash when archive redo log disk space runs out from running DELETE FROMs instead of TRUNCATEs; when the same raw number of INSERT operations doesn't seem to cause the same problem.

The only thing I can come up with: for UNDO, DELETE operations create the most UNDO volume because they store the "before" image of a row. INSERT operations create the least UNDO volume because they store only a ROWID. Seems that UNDO records are mirrored in the redo log files. Is this why large numbers of INSERTS seem to be less redo logfile intensive than the same number of DELETEs?

Wondering if I'm missing something about a symmetry or asymmetry between INSERTs and DELETEs and the volume of space they fill in the redo log files; vs. redo records only.


Dana Received on Fri Jul 17 2009 - 14:39:57 CDT

Original text of this message